程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 根據sql腳本修改數據庫表結構

根據sql腳本修改數據庫表結構

編輯:關於SqlServer

       最近由於項目需要要做一個小工具。

      需求:客戶用的老庫並存儲了一些數據,用了一段時間,我們根據客戶提出新功能在老庫的基礎上對新庫進行修改。這些修改有很多細節方面的修改,包含存儲過程,增加表,修改表字段類型,添加字段。

      然後我們自己更新並測試好軟件後,需要改動客戶那邊的老庫,老庫的數據是要保存的。

      解決方案一:很快我就想到用SQL SERVER 08 R2 自帶的功能,生成新庫腳本。把老庫改個名字,跑新庫腳本,然後通過數據庫自帶功能把老庫數據導入到新庫數據。測試的時候,數據量不大,速度還比較理想。

      但是這對客戶來說還是很不方便的,且數據量大些還是比較費時的。於是,boss就讓我做個小工具吧。

      難道我要用程序實現以上操作,oh my god !

      於是我想啊想…

      解決方案二:我就再細一些,直接操作數據庫的表吧,對於數據庫中存在的表,我給它重新命名,跑個新腳本建表,然後把老表中數據插進去。

      相關SQL語句如下:

      修改表名:EXEC sp_rename 'oldName' , 'newName'

      插入數據:insert into newTable(column1,column2,……) select column1,column2,…… from oldTable

      更多相關操作:

      參考數據庫、表、列的重命名

      列出參考內容來方便查看

      一、更改數據庫名

      sp_renamedb

      更改數據庫的名稱。

      語法

      sp_renamedb [ @dbname = ] ' old_name ' ,

      [ @newname = ] ' new_name '

      參數

      [ @dbname = ] ' old_name '

      是數據庫的當前名稱。old_name 為 sysname 類型,無默認值。

      [ @newname = ] ' new_name '

      是數據庫的新名稱。new_name 必須遵循標識符規則。new_name 為 sysname 類型,無默認值。

      返回代碼值

      0 (成功)或非零數字(失敗)

      權限

      只有 sysadmin 和 dbcreator 固定服務器角色的成員才能執行 sp_renamedb.

      示例

      下例將數據庫 accounting 改名為 financial.

      EXEC sp_renamedb ' accounting ' , ' financial '

      二、更改表名或列名

      sp_rename [ @objname = ] ' object_name ' ,

      [ @newname = ] ' new_name '

      [ , [ @objtype = ] ' object_type ' ]

      /////////////////////////////////////////////

      A. 重命名表

      下例將表 customers 重命名為 custs.

      EXEC sp_rename ' customers ' , ' custs '

      B. 重命名列

      下例將表 customers 中的列 contact title 重命名為 title.

      EXEC sp_rename ' customers.[contact title] ' , ' title ' , ' COLUMN '

      參考 復制表結構和表數據的SQL語句

      列出參考內容來方便查看

      1.復制表結構及數據到新表

      CREATE TABLE 新表 SELECT * FROM 舊表

      2.只復制表結構到新表

      CREATE TABLE 新表 SELECT * FROM 舊表 WHERE 1=2

      即:讓WHERE條件不成立。

      方法二:(由tianshibao提供)

      CREATE TABLE 新表 LIKE 舊表

      3.復制舊表的數據到新表(假設兩個表結構一樣)

      INSERT INTO 新表 SELECT * FROM 舊表

      4.復制舊表的數據到新表(假設兩個表結構不一樣)

      INSERT INTO 新表(字段1,字段2,……) SELECT 字段1,字段2,…… FROM 舊表

      想到這,發現好像還少了點什麼,呃,procedure和constraint,好吧,那我也先刪除再添加

      先查出來

      select name from sys.procedures

      select constraint_name, table_name from information_schema.table_constraints

      再刪除

      drop procedure [dbo].[procedure_name]

      alter table tableName drop CONSTRAINT [PK_ |FK_ |UK_ |DF_ |CK_]

      這裡再補充一些東西,關於約束前綴,參考SQL約束前綴

      方便查看,再列出來

      --主鍵

      constraint PK_字段 primary key(字段),

      --唯一約束

      constraint UK_字段 unique key(字段),

      --默認約束

      constrint DF_字段 default('默認值') for 字段,

      --檢查約束

      constraint CK_字段 check(約束。如:len(字段)>1),

      --主外鍵關系

      constraint FK_主表_從表 foreign(外鍵字段) references 主表(主表主鍵字段)

      然後再從腳本中讀取相應腳本文件並執行(加句廢話:要執行多行腳本文件,CommandText裡加'n'就好了)。

      解決方案三:思來想去,為什麼非要導入數據,直接改表結構就好了呀。於是讀取表中字段

      select column_name,data_type from information_schema.columns where table_name = 'tableName'

      對於舊表中存在的字段

      ALTER TABLE [tableName] ALTER COLUMN [columnName] [int] NOT NULL

      對於舊表中不存在的字段

      ALTER TABLE [tableName] ADD COLUMN [columnName] [int] NOT NULL

      這樣一來就不用導入數據就可以把舊庫結構更新,其中除了運用一些ADO.NET方面的知識,還用到了正則表達式之類的來匹配腳本中相應的SQL命令。當然目前的方案還存在一些問題,這個工具還不夠通用,對需要舊表多余的列並沒有刪除,還有容錯性方面考慮得也不夠周到。後期還會根據項目需要進一步改進的。

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved