数据库|sql使用系统表查询数据库

sql使用系统表查询
查询包含指定字段的所有表
select distinct Column_NameTable_name from information_schema.columns where Column_Name='CompanyCD2'
数据库名
select name from sysdatabases
表名
select nameid from sysobjects where xtype='U'
枚举列名
declare @objid int@objname char(40)
set @objname = '表名'
select @objid = id from sysobjects where id = object_id
(@objname)
select 'Column_name' = name from syscolumns where id =
@objid
order by colid
sql 查询所有数据库、表名、表字段总结
1、查询所有表
select [id
[name
from [sysobjects
where [type
= 'u' order by [name

2、查询所有数据库
3、select [name
from [sysdatabases
order by [name

查询表中字段
select [name
from [syscolumns
where [name
= 'tableXXX'order by [colid

SQL Server 查询所有表的记录数
SELECT object_name (i.id) TableName
rows as RowCnt
FROM sysindexes i
INNER JOIN sysObjects o
ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2
ORDER BY TableName
sql怎么查看表中的主键和主键列
select a.name 表名a.name 主键名 c.name 列名 from sys.key_constraints a
left join sys.index_columns b on a.parent_object_id=b.object_id
left join sys.columns c on b.object_id=c.object_id and b.column_id=c.column_id
【数据库|sql使用系统表查询】left join sysobjects d on a.parent_object_id=d.id where b.index_id=1