查询Sql Server数据库对象结构
- 查询数据库
- 查询架构
- 查询表
- 查询列
- 查询存储过程
- 查询视图
1.查询某一服务器下所有数据库
select t.[name] as 数据库
from sys.databases as t
order by name
2.查询某一数据库的架构
select name as 架构,
schema_id as 架构Id
--,principal_id
from sys.schemas
where principal_id = 1;
3.查询某一数据库的表
select s.name+'.'+t.name as 表,
t.object_id as 表Id
from sys.tables as t
inner join sys.schemas as s on t.schema_id = s.schema_id and s.principal_id=1
4.查询某一数据库的所有的列
select
s.name as 架构,
d.NAME as 表,
A.NAME as 列,
COLUMNPROPERTY( a.id,a.name,'IsIdentity') as 是否为自增, --0非自增 1自增
B.NAME AS 数据类型,
COLUMNPROPERTY(A.ID,A.NAME,'PRECISION') AS 精度,
ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'Scale'),0) AS 保留位,
A.ISNULLABLE AS 是否为空, -- 0空 1非空,
ISNULL(E.TEXT,'') as 默认值,
ISNULL(g.[value],'') AS 列说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sys.objects d ON a.id=d.object_id AND d.type='U'AND d.name<>'dtproperties'
inner join sys.schemas as s on d.schema_id=s.schema_id
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.object_id=f.major_id AND f.minor_id =0
5.查询存储过程
select
s.Name as 架构,
p.Name as 存储过程,
sm.definition as 存储过程内容
from sys.procedures as p
inner join sys.sql_modules sm ON p.object_id = sm.object_id
inner join sys.schemas as s on p.schema_id=s.schema_id
6.查询视图
select
s.name+'.'+t.name as 视图,
t.object_id as 视图Id
from sys.views as t
inner join sys.schemas as s on t.schema_id = s.schema_id and s.principal_id=1