---所有用户表(主键，外键，描述等信息）涂聚文 20150924 Geovin Du
SELECT
tbl.[name] AS [TableName],
clmns.[name] AS [ColumnName],
usrt.[name] AS [DataType],
ISNULL(baset.[name], N'') AS [SystemType],
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length],
CAST(clmns.xprec AS tinyint) AS [NumericPrecision],
CAST(clmns.xscale AS int) AS [NumericScale],
CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nulldata],
defaults.text AS [DefaultValue],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic,
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed],
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement],
cdef.[text] AS ComputedDefinition,
clmns.[collation] AS Collation,
CAST(clmns.colid AS int) AS ObjectId,
CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey,
CAST(ISNULL(IsForeignKey,0) AS bit) AS IsForeignKey,
ISNULL(pro.Description,'') AS ColumnDescription,
ISNULL(tpro.TableDescription,'') AS TableDescription
FROM
sys.sysobjects AS tbl
INNER JOIN sys.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN sys.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN sys.sysusers AS sclmns ON sclmns.uid = usrt.uid
LEFT JOIN sys.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN sys.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN sys.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA --是否主键
, o.name AS TABLE_NAME
, c.name AS COLUMN_NAME
, i.is_primary_key
FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.objects AS o ON i.object_id = o.object_id
LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1)AS pk ON tbl.name = pk.TABLE_NAME
AND pk.TABLE_SCHEMA='dbo'
AND pk.COLUMN_NAME=clmns.name
--是否外键
LEFT JOIN(SELECT
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column],
CAST(ISNULL(1, 0)AS bit)AS IsForeignKey
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id) AS fk ON tbl.name=fk.[table]
AND fk.schema_name='dbo'
AND fk.[column]=clmns.name
--列备注
LEFT JOIN (SELECT obj.name AS [TableName], col.name AS [ColumnName],pro.value AS [Description] FROM sys.columns col,sys.extended_properties pro,sys.objects obj
WHERE col.object_id=pro.major_id
AND col.column_id=pro.minor_id
AND obj.object_id=col.object_id) AS pro ON tbl.name=pro.TableName AND clmns.name=pro.ColumnName
--表备注
LEFT JOIN(SELECT obj.name AS TableName,pros.value AS TableDescription FROM sys.objects obj,sys.extended_properties pros
where obj.object_id=pros.major_id
AND pros.minor_id=0
and obj.type='u') AS tpro ON tbl.name=tpro.TableName
WHERE
tbl.[type] = 'U' ---(tbl.[type] = 'U' OR tbl.[type] = 'S')
--AND SCHEMA_NAME(tbl.uid) = @SchemaName
--AND tbl.[name] = @TableName
ORDER BY
tbl.name
GO
