ＳＱＬＳＥＲＶＥＲ查詢當前資料中的所有表

--切換資料庫
use c2
go


--查詢當前資料庫表名
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
c.name As '列名',
t.name As '列型別'
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name='base_dict' --指定表名
Order By c.colorder




--獲取當前資料庫指定列名在那些表中 [ 在修改資料庫中的某個欄位，但這個欄位又有關聯其他表，用這種方式一幕瞭然就能看出哪張表用了我這個欄位 ]
Select
m.name AS '表名',
c.name As '列名',
t.name As '列型別'
From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U' and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
And c.name='product_id'  --指定列名
Order By m.name




--獲取當前資料庫所有表列名和表型別
Select
m.name AS '表名',
c.name As '列名',
t.name As '列型別'
From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U' and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
Order By m.name