Ms SQL中 根據表名查詢欄位資訊，根據表名和欄位判斷該欄位是否存在

在ms sql中當我們需要修改表結構的時候，有時候因為添加或修改的表欄位存在或不存在而導致後面的sql指令碼執行失敗。<br />這時我們需要一個語句來判斷當前欄位的有效性。然後再執行我們的sql指令碼，這樣就可以避免了。<br />--根據表名查詢當前表的欄位及其對應的表<br />select b.name tableName, a.name fieldName<br />from syscolumns a join sysobjects b on a.id = b.id<br />where b.xtype = 'u' and b.name = 'mm_sample';<br />go<br />/*<br />xtype 的表示參數類型，通常包括如下這些<br />C = CHECK 條件約束<br />D = 預設值或 DEFAULT 約束<br />F = FOREIGN KEY 約束<br />L = 日誌<br />FN = 純量涵式<br />IF = 內嵌表函數<br />P = 預存程序<br />PK = PRIMARY KEY 約束（類型是 K）<br />RF = 複製篩選預存程序<br />S = 系統資料表<br />TF = 表函數<br />TR = 觸發器<br />U = 使用者表<br />UQ = UNIQUE 約束（類型是 K）<br />V = 視圖<br />X = 擴充預存程序<br />*/<br />--根據表名查詢欄位資訊<br />select name from syscolumns where id in (<br />select id from sysobjects where name = 'mm_sample'<br />)<br />go<br />select table_Name, column_Name from information_schema.columns<br />where table_name = 'mm_sample'<br />go<br />select name, length from syscolumns<br />where id = object_id( 'mm_sample ')<br />and colid in (<br />select colid from sysindexkeys where id = object_id( 'mm_sample ')<br />)<br />go<br />--select object_id('mm_sample')<br />--select * from sysobjects where id = object_id('mm_sample')<br />select name, * from syscolumns where id = object_id('mm_sample')<br />go<br />--查看錶資訊<br />sp_help 'mm_sample'<br />go<br />if (exists (select name from syscolumns where id = object_id('mm_sample') and name = 'wt_id'))<br />print '該欄位已經存在！';<br />else<br />print '該欄位不存在';<br />go</p><p>