List of View Columns With Details

This query returns list of views and their columns in a database sorted by schema, view and column name with details.

SQL Query

select schema_name(v.schema_id) AS SCHEMA_NAME,
       v.name AS VIEW_NAME,
       col.name AS COLUMN_NAME,
       t.name AS DATA_TYPE,
       t.name +
       case when t.is_user_defined = 0 then
                 isnull('(' +
                 case when t.name in ('binary', 'char', 'nchar',
                           'varchar', 'nvarchar', 'varbinary') then
                           case col.max_length
                                when -1 then 'MAX'
                                else
                                     case
                                         when t.name in ('nchar',
                                              'nvarchar') then
                                              cast(col.max_length/2
                                              as varchar(4))
                                         else cast(col.max_length
                                              as varchar(4))
                                     end
                           end
                      when t.name in ('datetime2',
                           'datetimeoffset', 'time') then
                            cast(col.scale as varchar(4))
                      when t.name in ('decimal', 'numeric') then
                           cast(col.precision as varchar(4)) + ', ' +
                           cast(col.scale as varchar(4))
                 end + ')', '')       
            else ':' +
                 (select c_t.name +
                         isnull('(' +
                         case when c_t.name in ('binary', 'char',
                                   'nchar', 'varchar', 'nvarchar',
                                   'varbinary') then
                                   case c.max_length
                                        when -1 then 'MAX'
                                        else case when t.name in
                                                       ('nchar',
                                                        'nvarchar')
                                                  then cast(c.max_length/2
                                                       as varchar(4))
                                                  else cast(c.max_length
                                                       as varchar(4))
                                             end
                                   end
                              when c_t.name in ('datetime2',
                                   'datetimeoffset', 'time') then
                                   cast(c.scale as varchar(4))
                              when c_t.name in ('decimal', 'numeric') then
                                   cast(c.precision as varchar(4)) +
                                   ', ' + cast(c.scale as varchar(4))
                         end + ')', '')
                    from sys.columns as c
                         inner join sys.types as c_t
                             on c.system_type_id = c_t.user_type_id
                   where c.object_id = col.object_id
                     and c.column_id = col.column_id
                     and c.user_type_id = col.user_type_id
                 )
       end AS DATA_TYPE_EXT,
       case when col.is_nullable = 0 then 'N' else 'Y' end as ISNULLABLE,
       ep.value as COMMENTS
  from sys.views as v
       join sys.columns as col
           on v.object_id = col.object_id
       left join sys.types as t
           on col.user_type_id = t.user_type_id
       left join sys.extended_properties as ep
           on v.object_id = ep.major_id
          and col.column_id = ep.minor_id
          and ep.name = 'MS_Description'
          and ep.class_desc = 'OBJECT_OR_COLUMN'
 order by SCHEMA_NAME,
       VIEW_NAME,
       COLUMN_NAME;

Rows

One row represents one view column. All view columns will be included.

Columns

Column	Description
SCHEMA_NAME	Schema name.
VIEW_NAME	View name.
COLUMN_NAME	View column name.
DATA_TYPE	Data type. Example: – varchar or decimal.
DATA_TYPE_EXT	Data type with information about scale/precision or string length. Example: – varchar(100) or decimal(8, 2).
ISNULLABLE	Nullable flag. “Y” if column is nullable, “N” if column is not nullable.
COMMENTS	Column comments.

Output

List of Views With Definition

This query returns list of views in a database sorted by schema and view name with their definition SQL and a comment.

SQL Query

select schema_name(v.schema_id) AS SCHEMA_NAME,
       v.name AS VIEW_NAME,
       v.create_date AS CREATE_DATE,
       v.modify_date AS LAST_MODIFIED,
       m.definition AS DEFINITION,
       ep.value AS COMMENTS
  from sys.views v
       left join sys.extended_properties ep
           on v.object_id = ep.major_id
          and ep.name = 'MS_Description'
          and ep.minor_id = 0
          and ep.class_desc = 'OBJECT_OR_COLUMN'
       inner join sys.sql_modules m
           on m.object_id = v.object_id
 order by SCHEMA_NAME,
          VIEW_NAME

Rows

One row represents one view. All views will be included.

Columns

Column	Description
SCHEMA_NAME	Schema name.
VIEW_NAME	View name.
CREATE_DATE	View creation date and time.
LAST_MODIFIED	View last modification date and time.
DEFINITION	View definition (SQL query).
COMMENTS	View comments.

Output

List of Foreign keys

This query returns list of tables and their foreign keys in a database.

SQL Query

select schema_name(tab.schema_id) AS TABLE_SCHEMA_NAME,
       tab.name AS TABLE_NAME,
       col.name AS COLUMN_NAME,
       fk.name AS CONSTRAINT_NAME,
       schema_name(tab_prim.schema_id) AS PRIMARY_TABLE_SCHEMA_NAME,
       tab_prim.name AS PRIMARY_TABLE_NAME,
       col_prim.name AS PRIMARY_TABLE_COLUMN,
       schema_name(tab.schema_id) + '.' + tab.name + '.' +
            col.name + ' = ' + schema_name(tab_prim.schema_id) + '.' +
            tab_prim.name + '.' + col_prim.name AS JOIN_CONDITION,
       case
            when count(*) over (partition by fk.name) > 1 then 'Y'
            else 'N'
       end AS COMPLEX_FK,
       fkc.constraint_column_id AS FK_PART
  from sys.tables as tab
       inner join sys.foreign_keys as fk
           on tab.object_id = fk.parent_object_id
       inner join sys.foreign_key_columns as fkc
           on fk.object_id = fkc.constraint_object_id
       inner join sys.columns as col
           on fkc.parent_object_id = col.object_id
          and fkc.parent_column_id = col.column_id
       inner join sys.columns as col_prim
           on fkc.referenced_object_id = col_prim.object_id
          and fkc.referenced_column_id = col_prim.column_id
       inner join sys.tables as tab_prim
           on fk.referenced_object_id = tab_prim.object_id
 order by TABLE_SCHEMA_NAME,
       TABLE_NAME,
       PRIMARY_TABLE_NAME,
       FK_PART;

Rows

One row represents one pair of columns in foreign key. All tables and their foreign keys will be included.

Columns

Column	Description
TABLE_SCHEMA_NAME	Foreign table schema name.
TABLE_NAME	Foreign table name.
COLUMN_NAME	Foreign table column name.
CONSTRAINT_NAME	Constraint name.
PRIMARY_TABLE_SCHEMA_NAME	Primary table schema name.
PRIMARY_TABLE_NAME	Primary table name.
PRIMARY_TABLE_COLUMN	Primary table column name.
JOIN_CONDITION	Join condition containing foreign and primary key tables and columns.
COMPLEX_FK	Complex foreign key flag. “Y” when foreign key is complex, otherwise “N”.
FK_PART	Represents part number of foreign key.

Output

Table Properties

This query returns some key table properties in the current database. 

SQL Query

SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id,
       p.data_compression_desc AS [Index Data Compression],
       t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter,
       t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_filetable,
          t.is_memory_optimized, t.durability_desc,
          t.temporal_type_desc, t.is_remote_data_archive_enabled, t.is_external -- new for SQL Server 2016
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON t.[object_id] = p.[object_id]
WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'
ORDER BY OBJECT_NAME(t.[object_id]), p.index_id OPTION (RECOMPILE);

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql