--------------------------------------------------------------------------------------------------
-- 1、SQL Server表结构查询SQL
--------------------------------------------------------------------------------------------------
SELECT
SO.name 表名,
SC.name 表列名,
ST.name 类型,
SC.length 长度,
case when exists(SELECT 1 FROMsysobjects where xtype='PK' and name in (SELECT name FROM sysindexes WHEREindid in(SELECT indid FROM sysindexkeys WHERE id = SC.id AND colid=SC.colid)))then '√' else '' end 主键,
case when SC.isnullable=1 then'√'else '' end 允许为空,
SC.colid 索引,
SC.colorder 字段序号
FROM
sysobjects SO, -- 对象表
syscolumns SC, -- 列名表
systypes ST -- 数据类型表
WHERE
SO.id = SC.id
AND SO.xtype = 'U' -- 类型U表示表,V表示视图
AND SO.status >=0 -- 非系统的临时表
AND SC.xtype = ST.xusertype
AND SO.name like 'BMP%'
ORDER BY
SO.name,SC.name -- 按表名、列名排序
-- ------------------------------------------------------------------------------------------------
-- 2. 如果从 ms sql 数据库中用一个复杂的sql语句,提取一个表的所有字段名称、类型、约束?
--------------------------------------------------------------------------------------------------
--表结构
SELECT
表名=case when a.colorder=1 thend.name else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROMsysobjects where xtype='PK' 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 ona.xtype=b.xusertype
inner join sysobjects d ona.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e ona.cdefault=e.id
left join sysproperties g ona.id=g.id and a.colid=g.smallid
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
--表的外键约束
SELECT 表对象ID=b.fkeyid
,表名称=object_name(b.fkeyid)
,引用的列ID=b.fkey
,引用的列名=(SELECT name FROM syscolumnsWHERE colid=b.fkey AND id=b.fkeyid)
,引用的表对象ID=b.rkeyid
,引用的表名=object_name(b.rkeyid)
,已引用的列ID=b.rkey
,已引用的列名=(SELECT name FROMsyscolumns WHERE colid=b.rkey AND id=b.rkeyid)
FROM sysobjects a
join sysforeignkeys b ona.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
--------------------------------------------------------------------------------------------------
-- 3、PostgreSQL表结构查询SQL
--------------------------------------------------------------------------------------------------
select
a.table_name,
a.column_name,
a.is_nullable,
a.udt_name,
a.character_maximum_length,
b.constraint_name,
c.constraint_type as pk_type,
d.indexrelname as index_name
from
information_schema.columns a
LEFT JOIN
information_schema.KEY_COLUMN_USAGEb
ON a.TABLE_NAME = b.table_name anda.column_name = b.column_name
LEFT JOIN
information_schema.TABLE_CONSTRAINTSc
ON b.table_name = c.table_name andb.constraint_name =c.constraint_name
LEFT JOIN
pg_stat_user_indexes d
ON c.TABLE_NAME = d.relname
where
a.table_name in ( select TABLE_NAME frominformation_schema.tables where table_schema = 'public' and table_type= 'BASETABLE' and is_insertable_into = 'YES')
order by
a.TABLE_NAME,a.COLUMN_NAME
利用DB字典表查询表格字段定义
最新推荐文章于 2023-10-16 14:13:39 发布