SQL Server: Get table primary key and Foreign Key using sql query

---所有用户表(主键，外键，描述等信息）涂聚文 20150924 Geovin DuSELECT    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_NAMEAND 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 IsForeignKeyFROM sys.foreign_key_columns fkcINNER JOIN sys.objects obj    ON obj.object_id = fkc.constraint_object_idINNER JOIN sys.tables tab1    ON tab1.object_id = fkc.parent_object_idINNER JOIN sys.schemas sch    ON tab1.schema_id = sch.schema_idINNER 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_idINNER 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 objWHERE col.object_id=pro.major_idAND col.column_id=pro.minor_idAND 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 proswhere obj.object_id=pros.major_idAND pros.minor_id=0and obj.type='u') AS tpro ON tbl.name=tpro.TableNameWHERE           tbl.[type] = 'U'    ---(tbl.[type] = 'U' OR tbl.[type] = 'S')            --AND SCHEMA_NAME(tbl.uid) = @SchemaName            --AND tbl.[name] = @TableName            ORDER BY              tbl.nameGO