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)
3 Comments. Leave new
Good one Pinal.But how about inserting the view output into a physical table and know the datatypes using sp_who.
Sorry i mean to say sp_help table name
sp_help returns multiple result sets. What exactly you want to store?