Monday, June 27, 2016

T-SQL: Summary of all Columns in DB

Verified in SQL 2008 R2:

SELECT  @@Servername AS Server ,
        DB_NAME() AS DBName ,
        isc.Table_Name AS TableName ,
        isc.Table_Schema AS SchemaName ,
        Ordinal_Position AS Ord ,
        Column_Name ,
        Data_Type ,
        Numeric_Precision AS Prec ,
        Numeric_Scale AS Scale ,
        Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX)
        Is_Nullable ,
        Column_Default ,
        Table_Type
        --,CASE WHEN ISNULL(col.Computed_Column,'0') = '0' THEN '0' ELSE '1' END as IsComputed
        ,col.definition as ComputedDefinition
FROM    INFORMATION_SCHEMA.COLUMNS isc
        INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name
LEFT OUTER JOIN (
SELECT  t.Name AS TableName ,
c.Column_ID AS Ord ,
c.Name AS Computed_Column
,cc.definition
FROM    sys.Tables t
INNER JOIN sys.Columns c ON t.object_id = c.object_id
INNER JOIN sys.computed_columns cc ON t.object_id = cc.object_id AND c.column_id = cc.column_id
--WHERE   c.is_computed = 1
--ORDER BY TableName, Ord
) col on col.TableName = isc.TABLE_NAME AND col.Computed_Column = COLUMN_NAME
ORDER BY DBName,TABLE_TYPE,TableName,SchemaName
,Ordinal_position

No comments: