请问如何获得一张表的主键和备注文字

SQL2005 SQL2008 表结构信息查询升级版（含外键信息）收藏
SELECT     表名=CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,     表说明=CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END ,     字段序号=a.colorder ,     字段名=a.name ,     标识=CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END ,     主键=CASE WHEN EXISTS (              SELECT 1               FROM dbo.sysindexes si                   INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid                   INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid                   INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'               WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√'               ELSE ''          END ,      外键=CASE WHEN tony.fkey is not null and tony.fkey=a.colid THEN '√' ELSE '' END ,      外键表=CASE WHEN tony.fkey is not null and tony.fkey=a.colid                 THEN object_name(tony.fkeyid) ELSE ''            END ,     外键字段=CASE WHEN tony.fkey is not null and tony.fkey=a.colid                  THEN (SELECT name FROM syscolumns                        WHERE colid=tony.fkey AND id=tony.fkeyid)                        ELSE ''              END,     类型=b.name ,      长度=a.length ,      精度=COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,      小数位数=ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,      允许空=CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END ,      默认值= ISNULL(e.text, ''),      字段说明=ISNULL(g.[value], ''),      创建时间=d.crdate,      更改时间=CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END FROM dbo.syscolumns a     LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype     INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0     LEFT JOIN dbo.syscomments e ON a.cdefault = e.id     LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id     LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0      LEFT JOIN sysobjects htl ON htl.parent_obj=d.id AND htl.xtype='F'     LEFT JOIN sysforeignkeys tony on htl.id=tony.constid WHERE d.name='tb'  --这里输入包含表名称的条件ORDER BY d.id, a.colorder 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/15/5489403.aspx
--sql server 2000
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder--sql server 2005
-- 1. 表结构信息查询
-- ========================================================================
-- 表结构信息查询
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
    TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
    TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
    Column_id=C.column_id,
    ColumnName=C.name,
    PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
    [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
    Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
    Type=T.name,
    Length=C.max_length,
    Precision=C.precision,
    Scale=C.scale,
    NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
    [Default]=ISNULL(D.definition,N''),
    ColumnDesc=ISNULL(PFD.[value],N''),
    IndexName=ISNULL(IDX.IndexName,N''),
    IndexSort=ISNULL(IDX.Sort,N''),
    Create_Date=O.Create_Date,
    Modify_Date=O.Modify_date
FROM sys.columns C
    INNER JOIN sys.objects O
        ON C.[object_id]=O.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
    INNER JOIN sys.types T
        ON C.user_type_id=T.user_type_id
    LEFT JOIN sys.default_constraints D
        ON C.[object_id]=D.parent_object_id
            AND C.column_id=D.parent_column_id
            AND C.default_object_id=D.[object_id]
    LEFT JOIN sys.extended_properties PFD
        ON PFD.class=1
            AND C.[object_id]=PFD.major_id
            AND C.column_id=PFD.minor_id
--             AND PFD.name='Caption'  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
    LEFT JOIN sys.extended_properties PTB
        ON PTB.class=1
            AND PTB.minor_id=0
            AND C.[object_id]=PTB.major_id
--             AND PFD.name='Caption'  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
    LEFT JOIN                       -- 索引及主键信息
    (
        SELECT
            IDXC.[object_id],
            IDXC.column_id,
            Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
                WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
            PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
            IndexName=IDX.Name
        FROM sys.indexes IDX
        INNER JOIN sys.index_columns IDXC
            ON IDX.[object_id]=IDXC.[object_id]
                AND IDX.index_id=IDXC.index_id
        LEFT JOIN sys.key_constraints KC
            ON IDX.[object_id]=KC.[parent_object_id]
                AND IDX.index_id=KC.unique_index_id
        INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
        (
            SELECT [object_id], Column_id, index_id=MIN(index_id)
            FROM sys.index_columns
            GROUP BY [object_id], Column_id
        ) IDXCUQ
            ON IDXC.[object_id]=IDXCUQ.[object_id]
                AND IDXC.Column_id=IDXCUQ.Column_id
                AND IDXC.index_id=IDXCUQ.index_id
    ) IDX
        ON C.[object_id]=IDX.[object_id]
            AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查询的表'       -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id -- 2. 索引及主键信息
-- ========================================================================
-- 索引及主键信息
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
    TableId=O.[object_id],
    TableName=O.Name,
    IndexId=ISNULL(KC.[object_id],IDX.index_id),
    IndexName=IDX.Name,
    IndexType=ISNULL(KC.type_desc,'Index'),
    Index_Column_id=IDXC.index_column_id,
    ColumnID=C.Column_id,
    ColumnName=C.Name,
    Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
        WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
    PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
    [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
    Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
    Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
    Fill_factor=IDX.fill_factor,
    Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
FROM sys.indexes IDX
    INNER JOIN sys.index_columns IDXC
        ON IDX.[object_id]=IDXC.[object_id]
            AND IDX.index_id=IDXC.index_id
    LEFT JOIN sys.key_constraints KC
        ON IDX.[object_id]=KC.[parent_object_id]
            AND IDX.index_id=KC.unique_index_id
    INNER JOIN sys.objects O
        ON O.[object_id]=IDX.[object_id]
    INNER JOIN sys.columns C
        ON O.[object_id]=C.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
            AND IDXC.Column_id=C.Column_id
--    INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
--    (
--        SELECT [object_id], Column_id, index_id=MIN(index_id)
--        FROM sys.index_columns
--        GROUP BY [object_id], Column_id
--    ) IDXCUQ
--        ON IDXC.[object_id]=IDXCUQ.[object_id]
--            AND IDXC.Column_id=IDXCUQ.Column_id
/**************************
系统数据库中查询表的所有字段以及描述（2008，2000区分）以及查询表的外键***************************/
----2008下-------方法一----表的扩展属性01------
SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 't_bill_in', 'column', default)SELECT
    CAST(value AS nvarchar(200)) as tableDescription
    FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'T_Bill_Cedula_Detail', default, default);
-----方法二----表的扩展属性描述-----
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    dbo.dtproperties  g
on
    a.id=g.id and a.colid=g.objectid
left join
    dbo.dtproperties  f
on
    d.id=f.id and f.objectid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder----方法03----表字段的描述（简易）
Select
col.[name]  as '字段名',
col.[length]as '长度'  ,
type.[name] as '类型'  ,
pro.value   as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join dbo.dtproperties as pro on col.id = pro.id and col.colid = pro.objectid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')
---2000下------------------------
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder=-----------方法02---表的描述2000下----
Select
col.[name]  as '字段名',
col.[length]as '长度'  ,
type.[name] as '类型'  ,
pro.value   as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join sysProperties as pro on col.id = pro.id and col.colid = pro.smallid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')
-----------------查询一个表的所有外键
SELECT 主键列ID=b.rkey
    ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
    ,外键表ID=b.fkeyid
    ,外键表名称=object_name(b.fkeyid)
    ,外键列ID=b.fkey
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
    ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
    ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
    join sysforeignkeys b on a.id=b.constid
    join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
    and object_name(b.rkeyid)='titles'SELECT *
FROM information_schema.columns
WHERE TABLE_CATALOG='数据库名'
    AND TABLE_NAME = '表名'
    AND COLUMN_NAME='列名'select *
from syscolumns
where id=object_id('tableName') and name='fieldName'------------2005以及2008中，查询表的字段---------------------DECLARE @tableName nvarchar(100)
SET @tableName ='tab'
SELECT  (
       CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名,
        a.colorder 字段序号, a.name 字段名,
        (CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识,
        (CASE WHEN (    SELECT COUNT(*)     FROM sysobjects     WHERE (name IN               (SELECT name               FROM sysindexes               WHERE (id = a.id) AND (indid IN                         (SELECT indid                         FROM sysindexkeys                       WHERE (id = a.id) AND (colid IN                     (SELECT colid                     FROM syscolumns                     WHERE (id = a.id) AND (name = a.name))))))) AND             (xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键,
        b.name 类型,
        a.length 占用字节数,
        COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
        ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
        (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空,
        ISNULL(e.text,'') 默认值, ISNULL(g.[value],'') AS 字段说明
        FROM syscolumns a
        LEFT JOIN systypes b ON a.xtype=b.xusertype
        INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties'
        LEFT JOIN syscomments e ON a.cdefault=e.id
        LEFT JOIN sys.extended_properties g  ON a.id=g.major_id AND a.colid = g.major_id WHERE d.name=@tableName
        ORDER BY a.id,a.colorder
SELECT
    CAST(value AS nvarchar(200)) as tableDescription
    FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 't_bank', default, default);declare @tablename varchar(100)
set @tablename=''
SELECT
       objname
    ,CAST(value AS nvarchar(200)) as fieldDescription
    FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName , 'column', default) AS E

调试易

请问如何获得一张表的主键和备注文字

解决方案 »