[點晴永久免費OA]如何查詢Sql Server中所有默認值約束并刪除它們
今天遇到一個問題，就是要將某數據庫中所有PNumber列刪除，這個數據庫基本上所有表都有這個字段，所以我寫了一段sql來刪除所有的PNumber列，如下：
declare @columnname nvarchar(1000) declare my_cur cursor local for select b.name from sys.syscolumns a inner join sys.tables b on a.id=b.object_id where a.name=''PreNumber'' open my_cur fetch next from my_cur into @columnname while @@fetch_status=0 begin exec (''alter table ''+@columnname+'' drop column PreNumber'') fetch next from my_cur into @columnname end close my_cur deallocate my_cur sql是沒有問題的，但執(zhí)行的時候報了類似下面的錯誤： 消息 5074，級別 16，狀態(tài) 1，第 2 行 對象''DF_XXXX_PNumber_Default'' 依賴于 列''PNumber''。 消息 4922，級別 16，狀態(tài) 9，第 2 行 由于一個或多個對象訪問此列，ALTER TABLE DROP COLUMN Creator 失敗。 原因就是創(chuàng)建PNumber列的時候為PNumber列創(chuàng)建了默認值，所以我們通過sql命令刪除時會要求我們先刪除對應的默認值約束(如果直接在設計器中刪除不會有此要求，設計器會同時刪除對應的約束)。 那么我們要怎么找出數據庫中所有表中PNumber列的默認值約束呢？這時候就需要利用sys.default_constraints目錄視圖了。 sys.default_constraints目錄視圖我們所有定義的默認值都可以通過這個目錄視圖查詢出來,sys.default_constraints中有幾個重要的列： 1，Name 約束名稱 2，parent_object_id 所屬表的表標識 3，parent_column_id 默認值對應列的列標識 4，definition 默認值的定義 5，is_system_named 約束名稱是不是自已定義的， 0代表是自己定義的，1代表是系統(tǒng)定義的。 有了這些信息，我可以鏈接sys.columns表與object_name函數查出默認值對應的表名與列名，sql如下： select name as 默認值名稱, object_name(t.parent_object_id) as 表名, (select sys.columns.name from sys.columns where sys.columns.column_id=t.parent_column_id and sys.columns.object_id=t.parent_object_id) as 列名, t.definition from sys.default_constraints t 如同批量刪除列一樣，我們可以寫一個游標，循環(huán)刪除所有的默認值。 declare @name varchar(100) declare @tablename varchar(100) declare my_cur cursor local for select a.name,a.tablename from (select name, object_name(t.parent_object_id) as tableName, (select sys.columns.name from sys.columns where sys.columns.column_id=t.parent_column_id and sys.columns.object_id=t.parent_object_id) as columnName, t.definition from sys.default_constraints t) a where columnname=''PNumber'' open my_cur fetch next from my_cur into @name,@tablename while @@fetch_status=0 begin exec (''alter table ''+@tablename+'' drop constraint ''+@name) fetch next from my_cur into @name,@tablename end close my_cur deallocate my_cur 查詢指定表cs_test2_list中字段time7是否存在約束值： select a.name from (select name,object_name(t.parent_object_id) as tableName,(select sys.columns.name from sys.columns where sys.columns.column_id=t.parent_column_id and sys.columns.object_id=t.parent_object_id) as columnName,t.definition from sys.default_constraints t) a where columnname=''time7'' and tablename=''cs_test2_list'' 該文章在 2019/9/28 20:48:02 編輯過 |
關鍵字查詢
相關文章
正在查詢... 
|
400 186 1886
