Earlier this week, I shared a script for finding stored procedure parameters and their data types in SQL Server. After publishing that blog post, I received several emails from my readers asking for a similar script for views. They liked the idea of getting both the base datatype and user-defined datatype in the result. Inspired by their requests, I decided to put in some extra effort and create a script specifically for views.

Before I share the script, let me explain how you can use it. You need to replace the view name and schema name in the script with your own values. Also, make sure you are in the same database that contains the view you want to analyze. For example, if your view is named “vEmployee” and belongs to the “HumanResources” schema, you would use the following code:

USE AdventureWorks2014;
GO

DECLARE @ViewName NVARCHAR(4000);
DECLARE @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;

Once you have replaced the necessary values, you can execute the script. It will list all the columns in the specified view along with their datatypes, whether they are part of the primary key, if they are foreign keys, and other relevant information.

I hope this script proves helpful in your SQL Server environments. If you have any suggestions for enhancements or any other feedback, I would love to hear from you. Feel free to reach out to me via email or leave a comment on this blog post.

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