查询sqlserver中某个表的定义的sql语句代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】查询sqlserver中某个表的定义的sql语句

作者:博文 / 发布于2017/11/20/ 471

DECLARE @tablename VARCHAR(MAX) --表名称
 SET @tablename='fnReceiveDtl'
 
 SELECT 
    t.name AS 'table_name', --表名称
	s.name AS 'column_name',   --列名称
	s2.name AS 'column_type_name', --列类型
	s.prec AS 'prec',   --列长度
	s.scale AS 'scale', --列精度
	s.collation AS 'collation', --列的比较和排序的定义
	ep.[value] AS 'column_description' --列的备注信息
 FROM 
    sys.tables AS t  --数据库中的表对象视图
	JOIN sys.syscolumns AS s --数据库中的列对象视图
	    ON s.id=t.[object_id]
	JOIN sys.systypes AS s2 --数据库中的类型视图
	    ON s2.xtype = s.xtype
    LEFT JOIN sys.extended_properties AS ep --数据库中的列备注视图
        ON ep.major_id=t.[object_id] AND ep.minor_id=s.colid
 WHERE t.name=@tablename
 ORDER BY s.colid

--主键约束
SELECT
  tab.name AS [表名],
  idx.name AS [主键名称],
  col.name AS [主键列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol
      ON (idx.object_id = idxCol.object_id 
          AND idx.index_id = idxCol.index_id 
          AND idx.is_primary_key = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id)
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id)
WHERE tab.name=@tablename

--唯一约束
SELECT
  tab.name AS [表名],
  idx.name AS [约束名称],
  col.name AS [约束列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol 
      ON (idx.object_id = idxCol.object_id
          AND idx.index_id = idxCol.index_id 
          AND idx.is_unique_constraint = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id)
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id)
WHERE tab.name=@tablename

--外键约束
select
  oSub.name  AS  [子表名称],
  fk.name AS  [外键名称],
  SubCol.name AS [子表列名],
  oMain.name  AS  [主表名称],
  MainCol.name AS [主表列名]
from
  sys.foreign_keys fk  
    JOIN sys.all_objects oSub 
        ON (fk.parent_object_id = oSub.object_id)
    JOIN sys.all_objects oMain 
        ON (fk.referenced_object_id = oMain.object_id)
    JOIN sys.foreign_key_columns fkCols
        ON (fk.object_id = fkCols.constraint_object_id)
    JOIN sys.columns SubCol 
        ON (oSub.object_id = SubCol.object_id  
            AND fkCols.parent_column_id = SubCol.column_id)
    JOIN sys.columns MainCol 
        ON (oMain.object_id = MainCol.object_id  
            AND fkCols.referenced_column_id = MainCol.column_id) 
WHERE oMain.name=@tablename

--Check约束
SELECT
  tab.name AS [表名],
  chk.name AS [Check约束名],
  col.name AS [列名],
  chk.definition
FROM
  sys.check_constraints chk
    JOIN sys.tables tab
      ON (chk.parent_object_id = tab.object_id)
    JOIN sys.columns col
      ON (chk.parent_object_id = col.object_id
          AND chk.parent_column_id = col.column_id)
WHERE tab.name=@tablename

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

同语言下

可能有用的

博文贡献的其它代码(10)