学步园

/********************************************
* 根据表名得到表信息，包括字段说明     
********************************************/   
CREATE PROC [dbo].[sp_help_table]
(@tableName VARCHAR(200), @ColumnLike VARCHAR(200) = NULL)         
AS   
--如果表名不存在，就直接选出相似表 
IF NOT EXISTS(
       SELECT 1
       FROM   sysobjects
       WHERE  id = OBJECT_ID(@tableName)
              AND TYPE = 'U'
   )
BEGIN
    SELECT NAME FROM   sysobjects
    WHERE  NAME LIKE '%' + @tableName + '%' AND TYPE = 'U'   
    RETURN
END 
 
 
--筛选相似列名
IF (@ColumnLike IS NULL)
    SET @ColumnLike = ''
   
DECLARE @ColumnTable TABLE(cName VARCHAR(200))   
INSERT @ColumnTable
  (
    cName
  )
SELECT a.name
FROM   syscolumns a,sysobjects d
WHERE  a.id = d.id
       AND d.name = @tableName
       AND a.name LIKE '%' + @ColumnLike + '%'   
     
--查询表结构信息            
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,
       字段说明 = ISNULL(g.[value], ''),
       标识 = 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, '')
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 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
                --where   d.name='要查询的表'         --如果只查询指定表,加上此条件
WHERE  d.name = @tableName
       AND EXISTS(
               SELECT 1
               FROM   @ColumnTable
               WHERE  cname = a.name
           )
ORDER BY a.id,a.colorder