【澳门新萄京】获得sql server数据库表构造

主干提醒:前天整治素材时观察有这么多个查询数据库中的表和字段音信的口舌

if exists (select 1 from sysobjects where name = ‘sysproperties’and
xtype = ‘V’)
begin
    DROP VIEW sysproperties
end
    GO
    CREATE VIEW sysproperties
AS
 SELECT class AS id,Minor_id AS sMallid,* from
sys.extended_properties 
 

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 ‘Y’ ELSE ‘N’ END) 允许空
,ISNULL(e.text ,”) 默认值
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

数据库中表太多,要查看有些表的布局比较困难,用此存款和储蓄进程比较平价;

明日整合治理资料时看到有如此叁个查询数据库中的表和字段音信的言辞,很强!
正是忘了那时何人写的了,印像中该是邹建所创。也贴出来现在备用吧。

——-在sql server 二零零六中得到表构造请先实践以上语句

where d.name in (‘DT_BLOG_CP’
,’mst_udc’)

 
CRM
/********************************************
* 依据表名获得表音讯,富含字段表达     
********************************************/   
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

1SELECT 2表名= CASE a.colorder WHEN 1 THEN c.name ELSE END, 3序=
a.colorder, 4字段名 = a.name, 5标识= CASE
COLUMNPROPERTY(a.id,a.name,IsIdentity) WHEN 1 THEN √ ELSE END, 6主键=
CASE 7WHEN EXISTS ( 8SELECT * 9FROM sysobjects 10WHERE xtype=PK AND
name IN ( 11SELECT name 12FROM sysindexes 13WHERE id=a.id AND indid IN (
14SELECT indid 15FROM sysindexkeys 16WHERE id=a.id AND colid IN (
17SELECT colid 18FROM syscolumns 19WHERE id=a.id AND name=a.name 20) 21)
22) 23) 24THEN √ 25ELSE 26END, 27类型= b.name, 28字节数 = a.length,
29长度= COLUMNPROPERTY(a.id,a.name,Precision), 30小数= CASE
ISNULL(COLUMNPROPERTY(a.id,a.name,Scale),0) 31WHEN 0 THEN 32ELSE
CAST(COLUMNPROPERTY(a.id,a.name,Scale) AS VARCHAR) 33END, 34允许空 =
CASE a.isnullable WHEN 1 THEN √ ELSE END, 35默认值 =
ISNULL(d.[text],), 36说明= ISNULL(e.[value],卡塔尔(قطر‎ 37FROM syscolumns a
38LEFTJOIN systypesb ON a.xtype=b.xusertype 39INNE福特Explorer JOIN sysobjectsc ON
a.id=c.id AND c.xtype=U AND c.namedtproperties 40LEFTJOIN syscommentsd
ON a.cdefault=d.id 41LEFTJOIN sysproperties e ON a.id=e.id AND
a.colid=e.smallid 42OKoleosDE奥迪Q3 BY c.name, a.colorder
作者校订一下,变个轻松版本的:

SELECT
  表名   = CASE a.colorder WHEN 1 THEN c.name ELSE ” END,
  序     = a.colorder,
  字段名 = a.name,
  标识   = CASE COLUMNPROPERTY(a.id,a.name,’IsIdentity’) WHEN 1 THEN ‘√’
ELSE ” END,
  主键   = CASE
    WHEN EXISTS (
      SELECT *
      FROM sysobjects
      WHERE xtype=’PK’ AND 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
          )
        )
      )
    )
    THEN ‘√’
    ELSE ”
  END,
  类型   = b.name,
  字节数 = a.length,
  长度   = COLUMNPROPERTY(a.id,a.name,’Precision’),
  小数   = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,’Scale’),0)
    WHEN 0 THEN ”
    ELSE CAST(COLUMNPROPERTY(a.id,a.name,’Scale’) AS VARCHAR)
  END,
  允许空 = CASE a.isnullable WHEN 1 THEN ‘√’ ELSE ” END,
  默认值 = ISNULL(d.[text],”),
  说明   = ISNULL(e.[value],”)
FROM syscolumns a
  LEFT  JOIN systypes      b ON a.xtype=b.xusertype
  INNECRUISER JOIN sysobjects    c ON a.id=c.id AND c.xtype=’U’ AND
c.name<>’dtproperties’ and c.name=’此处改为您获得的表名称’
  LEFT  JOIN syscomments   d ON a.cdefault=d.id
  LEFT  JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid
ORDER BY c.name, a.colorder

ORDER BY
d.name,
a.id
,a.colorder

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图