Columns List of Views

One of myclient asked me nice question about getting columns list for views. Well, please take a look at following solution I came up with.

The first view just holds the data about view and their columns and as you can see it is not easy to read it at all.

CREATE VIEW v_getcolumns
AS
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME ,COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME=INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA) + '.' +
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
), 'IsMSShipped') = 0


The final step I build a crosstab to contacanate the names of the columns per view based on count.I ened upi with 10 columns per view as maximum number, you can modify it for your needs.The cool thing is if you specify '*' in SELECT statement within a view and running the following query you get all columns of the table.

SELECT TABLE_NAME ,MAX(CASE WHEN rnk = 1 then COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 2 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 3 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 4 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 5 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 6 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 7 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 8 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 9 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 10 THEN ', ' + COLUMN_NAME ELSE '' end) AS columns
FROM
(
SELECT A.TABLE_NAME ,A.COLUMN_NAME,COUNT(*)rnk
FROM v_getcolumns A, v_getcolumns B
WHERE A.TABLE_NAME=B.TABLE_NAME AND
A.COLUMN_NAME>=B.COLUMN_NAME
GROUP BY a.TABLE_NAME ,a.COLUMN_NAME
) AS der
GROUP BY TABLE_NAME

T-SQL and CLR types do not match

As you know in SQL Server 2005 you can reference to .NET classes doing some operations that in T-SQL is realy hard to perform. For example if you need to format very long and 'complicated' string value , so by using .NET String method you can easily achive it. But I'd like to show you another example actually posted by Itzik Ben-Gan on his website ,so I just modified his C# code to VB.NET that is invoking the ToString method of the SqlDateTime datatype with a format string.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class Valid
"<"SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True)">" _
Public Shared Function fn_format_datetime(ByVal dt As SqlTypes.SqlDateTime, _
ByVal formatstring As SqlTypes.SqlString) As String
If dt.IsNull Or formatstring.IsNull Then
Return formatstring.IsNull()
Else
Return dt.Value.ToString(formatstring.Value)
End If

End Function

End Class

I assume you are familiar with .NET technology , so I build a solution and try to reference to this function within my database.I created ASSEMBLY without problem but when I tried to create a function I got the error "CREATE FUNCTION for "fn_format_datetime1" failed because T-SQL and CLR types for return value do not match."

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as VARCHAR(100))RETURNS VARCHAR(100)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

As you see T-SQL datatype VARCHAR(n) does not match .NET STRING class. I should use NVARCHAR(n) datatype to get it working, did not know it.

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as NVARCHAR(4000))RETURNS NVARCHAR(4000)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

--Usage
SELECT
dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),
dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');