获取SQL数据库、表、视图、列信息

 --获取表的名称及用表中第一列的注释作表的说明，形成响应的SQL语句
select 'delete from ',Name2 ,'--',RANK()OVER  (ORDER BY Name2 DESC) as 序号,字段说明 from 
(
select bb.name as Name2,cc.* from (

select min(minor_id) as minor_id,id from (
SELECT g.minor_id minor_id,a.Name as Name ,a.Length as Len,b.Name as [Type],ISNULL(g.[value], '') AS 字段说明 ,a.id
--select * 
FROM  syscolumns as a     
left join systypes b on a.xusertype=b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U'
 AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT 
OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND
 g.name = 'MS_Description' LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
 AND f.name = 'MS_Description'where b.Name <> 'sysname' --group by a.id
--AND a.id=object_id('dbo.T_CarInfo')
) gg group by id

)as cc
,
(
SELECT name,id FROM sysobjects where xtype= 'u ') as bb
where bb.id=cc.id
) as dA LEFT JOIN
(
SELECT g.minor_id minor_id,a.Name as Name ,a.Length as Len,b.Name as [Type],ISNULL(g.[value], '') AS 字段说明, a.id as idd,CRDATE
--select * 
FROM  syscolumns as a     
left join systypes b on a.xusertype=b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U'
 AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT 
OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND
 g.name = 'MS_Description' LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
 AND f.name = 'MS_Description'where b.Name <> 'sysname'
) 
as dB ON dA.minor_id=dB.minor_id and id=idd

----选择master数据库，查询所有数据库名称
select name,filename from sysdatabases order by name
----选择具体的数据库，查询数据库包含的表
select [name] from sysobjects where xtype='U'and [name]<>'dtproperties' order by [name]
----查询数据库的视图
select [name] from sysobjects where xtype='V' and [name]<>'syssegments' and [name]<>'sysconstraints' order by [name]
----查询表、视图信息
select [name],xtype from sysobjects where (xtype='U'or xtype='V') and [name] not in ('sysdiagrams','') order by [name]
--查询视图对应的表名
select Table_Name from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where View_Name='V_AMeals'--视图名称

--查询一张表、视图的具体信息
SELECT 
colorder=C.column_id,
ColumnName=C.name,
TypeName=T.name, 
Length=CASE WHEN T.name='nchar' THEN C.max_length/2 WHEN T.name='nvarchar' THEN C.max_length/2 ELSE C.max_length END,
Preci=C.precision, 
Scale=C.scale, 
IsIdentity=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
isPK=ISNULL(IDX.PrimaryKey,N''),
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, 
IndexName=ISNULL(IDX.IndexName,N''), 
IndexSort=ISNULL(IDX.Sort,N''), 
Create_Date=O.Create_Date,
 Modify_Date=O.Modify_date, 
cisNull=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, 
defaultVal=ISNULL(D.definition,N''), 
deText=ISNULL(PFD.[value],N'') 
FROM sys.columns C 
INNER JOIN sys.objects O 
ON C.[object_id]=O.[object_id] 
AND (O.type='U' or O.type='V')
 AND O.is_ms_shipped=0 
INNER JOIN sys.types T 
ON C.user_type_id=T.user_type_id
 LEFT JOIN sys.default_constraints D 
ON C.[object_id]=D.parent_object_id 
AND C.column_id=D.parent_column_id 
AND C.default_object_id=D.[object_id] 
LEFT JOIN sys.extended_properties PFD 
ON PFD.class=1  
AND C.[object_id]=PFD.major_id  
AND C.column_id=PFD.minor_id 
LEFT JOIN sys.extended_properties PTB 
ON PTB.class=1 
AND PTB.minor_id=0  
AND C.[object_id]=PTB.major_id 
LEFT JOIN 
( 
SELECT  
IDXC.[object_id], 
IDXC.column_id,
 Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') 
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
 PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, 
IndexName=IDX.Name 
FROM sys.indexes IDX 
INNER JOIN sys.index_columns IDXC 
ON IDX.[object_id]=IDXC.[object_id] 
AND IDX.index_id=IDXC.index_id 
LEFT JOIN sys.key_constraints KC 
ON IDX.[object_id]=KC.[parent_object_id] 
AND IDX.index_id=KC.unique_index_id 
INNER JOIN  
( 
SELECT [object_id], Column_id, index_id=MIN(index_id) 
FROM sys.index_columns 
GROUP BY [object_id], Column_id 
) IDXCUQ 
ON IDXC.[object_id]=IDXCUQ.[object_id] 
AND IDXC.Column_id=IDXCUQ.Column_id 
AND IDXC.index_id=IDXCUQ.index_id 
) IDX 
ON C.[object_id]=IDX.[object_id]
 AND C.column_id=IDX.column_id  
WHERE O.name=N'T_AMeals' --表名、视图名
ORDER BY O.name,C.column_id