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

---所有用户表(主键，外键，描述等信息）涂聚文 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