DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

SQL Columns Metainfo : "vw_XL_TableColumns"

10.11.2011
| 2053 views |
  • submit to reddit
        CREATE VIEW dbo.vw_XL_TableColumns  
AS  
SELECT     TOP (100) PERCENT tabs.name AS TableName, col.name AS ColumnName, coltype.name AS TypeName, col.max_length AS MaxLength, col.precision, col.scale,   
                      CASE WHEN col.name = pkCol.COLUMN_NAME THEN 'Y' ELSE 'N' END AS IsPrimaryKey, CASE WHEN col.is_nullable = 1 THEN 'Y' ELSE 'N' END AS IsNullable,   
                      CASE WHEN col.is_identity = 1 THEN 'Y' ELSE 'N' END AS IsIdentity, CASE WHEN col.is_computed = 1 THEN 'Y' ELSE 'N' END AS IsComputed,   
                      col.column_id AS ColumnOrder  
FROM         sys.columns AS col INNER JOIN  
                      sys.tables AS tabs ON col.object_id = tabs.object_id INNER JOIN  
                      sys.systypes AS coltype ON coltype.xtype = col.system_type_id LEFT OUTER JOIN  
                          (SELECT     c.COLUMN_NAME, c.TABLE_NAME  
                            FROM          INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pk INNER JOIN  
                                                   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c ON pk.TABLE_NAME = c.TABLE_NAME AND   
                                                   pk.CONSTRAINT_NAME = c.CONSTRAINT_NAME  
                            WHERE      (pk.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS pkCol ON pkCol.TABLE_NAME = tabs.name AND pkCol.COLUMN_NAME = col.name  
ORDER BY TableName, ColumnOrder