Interview Question of the Week #004 – List All Columns and Their Data Type for a View

Earlier this week I wrote a blog about finding stored procedure parameters and their data types. After that blog I received few emails from my blog readers asking for similar script for a view. I asked them what did they like about the script and they said that it gives us base datatype and user defined data type as well. So I have put some more efforts to write similar script about view. This is a fine example of how each one of you out there inspire me with some great content ideas. These keep these emails coming my way.

Here is the question – How to list all columns and their datatype for a view in SQL Server?

Usage of script

To use the below script you need to replace the view name (vEmployee in sample code) and schema name (HumanResources in sample code). Also make sure you are in same database which has stored procedure (AdventureWorks2014 is sample code):

USE AdventureWorks2014
GO
DECLARE  @ViewName NVARCHAR(4000)
,@SchemaName NVARCHAR(4000)
SELECT   @ViewName = N'vEmployee'
,@SchemaName = N'HumanResources'
SELECT c.NAME AS [Name]
,CAST(ISNULL(ic.index_column_id, 0) AS BIT) AS [InPrimaryKey]
,CAST(ISNULL((
SELECT TOP 1 1
FROM sys.foreign_key_columns AS colfk
WHERE colfk.parent_column_id = c.column_id
AND colfk.parent_object_id = c.OBJECT_ID
), 0) AS BIT) AS [IsForeignKey]
,u_t.NAME AS [DataType]
,ISNULL(b_t.NAME, N'') AS [SystemType]
,CAST(CASE
WHEN b_t.NAME IN (
N'nchar'
,N'nvarchar'
)
AND c.max_length <> - 1
THEN c.max_length / 2
ELSE c.max_length
END AS INT) AS [Length]
,CAST(c.PRECISION AS INT) AS [NumericPrecision]
,CAST(c.scale AS INT) AS [NumericScale]
,c.is_nullable AS [Nullable]
,c.is_computed AS [Computed]
,ISNULL(s.NAME, N'') AS [XmlSchemaNamespaceSchema]
,ISNULL(xsc.NAME, N'') AS [XmlSchemaNamespace]
,ISNULL((
CASE c.is_xml_document
WHEN 1
THEN 2
ELSE 1
END
), 0) AS [XmlDocumentConstraint]
,CAST(c.is_sparse AS BIT) AS [IsSparse]
,CAST(c.is_column_set AS BIT) AS [IsColumnSet]
,c.column_id AS [ID]
FROM sys.all_views AS v
INNER JOIN sys.all_columns AS c ON c.OBJECT_ID = v.OBJECT_ID
LEFT JOIN sys.indexes AS i ON i.OBJECT_ID = c.OBJECT_ID
AND 1 = i.is_primary_key
LEFT JOIN sys.index_columns AS ic ON ic.index_id = i.index_id
AND ic.column_id = c.column_id
AND ic.OBJECT_ID = c.OBJECT_ID
AND 0 = ic.is_included_column
LEFT JOIN sys.types AS u_t ON u_t.user_type_id = c.user_type_id
LEFT JOIN sys.types AS b_t ON (
b_t.user_type_id = c.system_type_id
AND b_t.user_type_id = b_t.system_type_id
)
OR (
(b_t.system_type_id = c.system_type_id)
AND (b_t.user_type_id = c.user_type_id)
AND (b_t.is_user_defined = 0)
AND (b_t.is_assembly_type = 1)
)
LEFT JOIN sys.xml_schema_collections AS xsc ON xsc.xml_collection_id = c.xml_collection_id
LEFT JOIN sys.schemas AS s ON s.schema_id = xsc.schema_id
WHERE (v.TYPE = 'V')
AND (
v.NAME = @ViewName
AND SCHEMA_NAME(v.schema_id) = @SchemaName
)
ORDER BY [ID] ASC

Here is the sample execution. I have highlighted the modification needed to use the script.

I hope these scripts will help you in your environments. I would love to hear back from you how these can be enhanced if possible.

Reference: Pinal Dave (https://blog.sqlauthority.com)