查询Sql Server数据库对象结构

1. 查询数据库
2. 查询架构
3. 查询表
4. 查询列
5. 查询存储过程
6. 查询视图

---

1.查询某一服务器下所有数据库

select t.[name] as 数据库from sys.databases as torder by name

2.查询某一数据库的架构

select name as 架构,    schema_id as 架构Id    --,principal_idfrom sys.schemaswhere principal_id = 1;

3.查询某一数据库的表

select s.name+'.'+t.name as 表,    t.object_id as 表Idfrom sys.tables as tinner 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.xusertypeINNER 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_idLEFT 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  pinner join sys.sql_modules sm ON p.object_id = sm.object_idinner join sys.schemas as s on p.schema_id=s.schema_id

6.查询视图

select     s.name+'.'+t.name as 视图,    t.object_id as 视图Idfrom sys.views as tinner join sys.schemas as s on t.schema_id = s.schema_id and s.principal_id=1