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
Monday, June 27, 2016
T-SQL: Summary of all Columns in DB
Verified in SQL 2008 R2:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment