SDU Tools: List Columns and Data Types in SQL Server Views
Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. I do a lot of reviewing of database designs and one thing I always check for is the use of data types, including when they’ve been used in views. So we added a tool that can help to find these. It’s called ListViewColumnsAndDataTypes.
The procedure takes four parameters:
@DatabaseName is the database to process (not used in the Azure SQL DB version) @SchemasToList is a comma-delimited list of schema names or ALL @ViewsToList is a comma-delimited list of view names or ALL @ColumnsToList is a comma-delimited list of column names or ALL
The rowset returned has the following columns: SchemaName, ViewName, ColumnName, DataType and SystemDataType. Within each table, the columns are listed in Column ID order.
Find out more
You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:
You can use our tools as a set or as a great example of how to write functions like these.
Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:
http://sdutools.sqldownunder.com
Latest version of the code
Note: the code might wrap when displayed below. The Azure SQL DB version that only operates on the current database is simpler and can be seen by downloading the tools.
CREATE PROCEDURE SDU_Tools.ListViewColumnsAndDataTypes
@DatabaseName sysname,
@SchemasToList nvarchar(max) = N'ALL', -- N'ALL' for all
@ViewsToList nvarchar(max) = N'ALL', -- N'ALL' for all
@ColumnsToList nvarchar(max) = N'ALL' -- N'ALL' for all
AS
BEGIN
-- Function: Lists the data types for all columns in views
-- Parameters: @DatabaseName sysname -> Database to process
-- @SchemasToList nvarchar(max) -> 'ALL' or comma-delimited list of schemas to list
-- @ViewsToList nvarchar(max) -> 'ALL' or comma-delimited list of views to list
-- @ColumnsToList nvarchar(max) -> 'ALL' or comma-delimited list of columns to list
-- Action: Lists the data types for all columns (user views only)
-- Return: Rowset containing SchemaName, ViewName, ColumnName, DataType and SystemDataType.
-- Within each table, columns are listed in column ID order
-- Refer to this video: https://youtu.be/kuJBf6R8P2Q
--
-- Test examples:
/*
EXEC SDU_Tools.ListViewColumnsAndDataTypes @DatabaseName = N'WideWorldImporters',
@SchemasToList = N'ALL',
@ViewsToList = N'ALL',
@ColumnsToList = N'ALL';
*/
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @SQL nvarchar(max) =
' SELECT s.[name] AS SchemaName,
v.[name] AS ViewName,
c.[name] AS ColumnName,
typ.[name] + CASE WHEN typ.[name] IN (N''decimal'', N''numeric'')
THEN N''('' + CAST(c.precision AS nvarchar(20)) + N'', ''
+ CAST(c.scale AS nvarchar(20)) + N'')''
WHEN typ.[name] IN (N''varchar'', N''nvarchar'', N''char'', N''nchar'', N''binary'', N''varbinary'')
THEN N''('' + CASE WHEN c.max_length < 0
THEN N''max''
WHEN typ.[name] IN (N''nvarchar'', N''nchar'')
THEN CAST(c.max_length / 2 AS nvarchar(20))
ELSE CAST(c.max_length AS nvarchar(20))
END + N'')''
WHEN typ.[name] IN (N''time'', N''datetime2'', N''datetimeoffset'')
THEN N''('' + CAST(c.scale AS nvarchar(20)) + N'')''
ELSE N''''
END AS DataType,
typs.[name] + CASE WHEN typs.[name] IN (N''decimal'', N''numeric'')
THEN N''('' + CAST(c.precision AS nvarchar(20)) + N'', ''
+ CAST(c.scale AS nvarchar(20)) + N'')''
WHEN typs.[name] IN (N''varchar'', N''nvarchar'', N''char'', N''nchar'', N''binary'', N''varbinary'')
THEN N''('' + CASE WHEN c.max_length < 0
THEN N''max''
WHEN typs.[name] IN (N''nvarchar'', N''char'')
THEN CAST(c.max_length / 2 AS nvarchar(20))
ELSE CAST(c.max_length AS nvarchar(20))
END + N'')''
WHEN typs.[name] IN (N''time'', N''datetime2'', N''datetimeoffset'')
THEN N''('' + CAST(c.scale AS nvarchar(20)) + N'')''
ELSE N''''
END AS SystemDataType
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.views AS v
ON s.[schema_id] = v.[schema_id]
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
ON v.[object_id] = c.[object_id]
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.[types] AS typ
ON c.system_type_id = typ.system_type_id
AND c.user_type_id = typ.user_type_id
INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.[types] AS typs
ON typs.system_type_id = c.system_type_id
AND typs.user_type_id = c.system_type_id
WHERE v.[type] = N''V'''
+ CASE WHEN @SchemasToList = N'ALL'
THEN N''
ELSE N' AND s.[name] IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @SchemasToList + ''', N'','', 1))'
END + @CRLF
+ CASE WHEN @ViewsToList = N'ALL'
THEN N''
ELSE N' AND t.[name] IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @ViewsToList + ''', N'','', 1))'
END + @CRLF
+ CASE WHEN @ColumnsToList = N'ALL'
THEN N''
ELSE N' AND c.[name] IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
+ @ColumnsToList + ''', N'','', 1))'
END + @CRLF
+ N' ORDER BY SchemaName, ViewName, c.column_id;';
EXEC (@SQL);
END;
2025-03-31