[SQL Server] 寫程式跑迴圈為資料庫每個Table加欄位和描述

2014-05-01

8021
0
SQL Server
2014-05-02

前言

最近某案子都快做完了，客戶才說資料表要加新增、異動時間和是否刪除的欄位

一個資料庫那麼多Table，要我一個一個打開設計去編輯也太浪費時間

寫了兩個script放上來備份

實作

整個概念大致是：

1.宣告游標走訪目前資料庫的所有Table名稱

2.判斷該Table沒有那三個欄位的話，就Add Column

不過要注意的是Alter Table 後面由於沒辦法接變數名稱

所以整個Alter Table的SQL語法要讓它變成一個SQL字串餵給Exec() 來執行





--宣告承接資料的變數
Declare @name nvarchar(100)
--宣告指標走訪資料庫的Table名稱
Declare myCursor cursor for
                        select [name] from sys.tables Where [type]='U'

open myCursor
 fetch next from myCursor into @name
 while(@@FETCH_STATUS=0)
 Begin
 --如果沒有InsertDatetime資料行...
  if not exists(select * from sys.columns 
            where Name = N'InsertDatetime' and Object_ID = Object_ID(@name))
		begin
		  --就建立
		   Exec ('ALTER TABLE ' + @name + ' ADD  InsertDatetime Datetime  NOT NULL   DEFAULT Getdate() ')
		    
		end
		--如果沒有UpdateDatetime資料行...
  if not exists(select * from sys.columns 
            where Name = N'UpdateDatetime' and Object_ID = Object_ID(@name))
		begin
		  --就建立
		   Exec ('ALTER TABLE ' + @name + ' ADD  UpdateDatetime Datetime  NOT NULL   DEFAULT Getdate() ')
		    
		end
		--如果沒有IsDel資料行...
  if not exists(select * from sys.columns 
            where Name = N'IsDel' and Object_ID = Object_ID(@name))
		begin
		  --就建立
		   Exec ('ALTER TABLE ' + @name + ' ADD  IsDel bit  NOT NULL   DEFAULT 0 ')
		    
		end
 




 fetch next from myCursor into @name
 End
close myCursor
deallocate myCursor

以下是加描述的語法





--宣告承接資料的變數
Declare @name nvarchar(100)
--宣告指標走訪資料庫的Table名稱
Declare myCursor cursor for
                        select [name] from sys.tables Where [type]='U'

open myCursor
 fetch next from myCursor into @name
 while(@@FETCH_STATUS=0)
 Begin
 --如果有InsertDatetime資料行...
  if  exists(select * from sys.columns 
            where Name = N'InsertDatetime' and Object_ID = Object_ID(@name))
		begin
		 

		 --為資料行加入描述
		EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'資料新增時間' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', 
@level1name=@name, @level2type=N'COLUMN', @level2name=N'InsertDatetime'

		end

		--如果有UpdateDatetime資料行...
  if  exists(select * from sys.columns 
            where Name = N'UpdateDatetime' and Object_ID = Object_ID(@name))
		begin
		 

		 --為資料行加入描述
		EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'資料異動時間' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', 
@level1name=@name, @level2type=N'COLUMN', @level2name=N'UpdateDatetime'

		end
		--如果有IsDel資料行...
  if  exists(select * from sys.columns 
            where Name = N'IsDel' and Object_ID = Object_ID(@name))
		begin
		 

		 --為資料行加入描述
		EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'是否刪除' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', 
@level1name=@name, @level2type=N'COLUMN', @level2name=N'IsDel'

		end
	  
 fetch next from myCursor into @name
 End
close myCursor
deallocate myCursor

2014.5.2 追記

如果要移除某幾個Table的某欄位的話

參考寫法(移除Table名稱Log結尾且擁有UpdateAccount欄名的欄位)


--宣告指標
 Declare myCursor cursor 
  for select [name] from sys.tables
       Where name Like '%log' or name like '%logs'
 --宣告承接資料的指標
 Declare @tablename varchar(100)

 open myCursor
   fetch from myCursor into @tablename
   while(@@FETCH_STATUS=0)
   begin 

  if  exists(select * from sys.columns 
            where Name = N'UpdateAccount' and Object_ID = Object_ID(@tablename))
  Begin 
   --找出column_id
 Declare @column_id  int

 select @column_id=column_id from sys.columns
 Where name='UpdateAccount' and object_id=object_id(@tablename)


			 --移除default_constraints
			   if exists (
			 select [name] from sys.default_constraints
			 where [type] = 'D' and parent_object_id=Object_ID(@tablename)  and parent_column_id=@column_id)
			  begin 
			  Declare @name varchar(1000)
			  select @name = [name] from sys.default_constraints
			 where [type] = 'D' and parent_object_id=Object_ID(@tablename)  and parent_column_id=@column_id
			  Exec ( 'ALTER TABLE '+@tablename+' DROP CONSTRAINT ' + @name)
   
			  end 
   --移除UpdateAccount

  Exec( 'ALTER TABLE '+@tablename +' DROP COLUMN UpdateAccount' )

  End 


   fetch from myCursor into @tablename
   end 

 close myCursor
 deallocate myCursor

結語

寫T-SQL跑程式的方式來為所有Table加欄位，比較省時

執行了一下，上完廁所回來已經跑完了

參考文章

http://forums.asp.net/t/1362648.aspx?how+to+add+comments+on+table+and+columns+in+TSQL+that+is+Microsoft+sql

How to check if column exists in SQL Server table

How to add a comment to an existing table column in SQL Server? [duplicate]

Add a column, with a default value, to an existing table in SQL Server

Drop default constraint on a column in TSQL

回首頁