批量修改数据库排序规则,含主外键

时间:2022-03-29 07:19:21

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2011-05-17 17:25:07
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

---以下脚本未进行大规模测试,操作前请先备份数据库,以便出问题时还原。有问题请留言,Ths。
--
1.生成主键约束脚本并引出
SELECT 'ALTER TABLE '
    
+ QUOTENAME(a.TABLE_NAME)
    
+ ' ADD CONSTRAINT '
    
+ a.CONSTRAINT_NAME
    
+ ' PRIMARY KEY ('
    
+ QUOTENAME(COLUMN_NAME)
    
+');' AS [Parmary Key SQL]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
   
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
       
ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE='PRIMARY KEY'

--2.生成外键约束脚本并引出
SELECT 'ALTER TABLE '
   
+ QUOTENAME(OBJECT_NAME(a.PARENT_OBJECT_ID)) --表名
    + ' ADD CONSTRAINT '
   
+ OBJECT_NAME(a.OBJECT_ID) --约束名
    + ' FOREIGN KEY ('
   
+ QUOTENAME(c.name) --字段名
    + ') REFERENCES '
   
+ QUOTENAME(OBJECT_NAME(a.REFERENCED_OBJECT_ID))--被引用表名
    + ' ('
   
+ QUOTENAME(d.name)--被引用字段名
    + ')'
   
+ CASE WHEN a.delete_referential_action=1
          
THEN ' ON DELETE CASCADE '
          
ELSE ''
     
END
   
+ CASE WHEN update_referential_action=1
          
THEN ' ON UPDATE CASCADE '
          
ELSE ''
     
END
   
+';' AS [Foreing Key SQL]
FROM sys.foreign_keys a
   
JOIN  sys.foreign_key_columns b
       
ON a.[object_id]=b.constraint_object_id
   
JOIN sys.[columns] c
       
ON b.parent_object_id=c.[object_id]
           
AND b.parent_column_id=c.column_id
   
join sys.[columns] d
       
ON b.referenced_object_id=d.[object_id]
           
AND b.referenced_column_id=d.column_id

--3.删除外键约束
SET NOCOUNT ON
DECLARE c1 cursor for
    
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    
from sysobjects
    
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    
begin
        
exec(@c1)
        
fetch next from c1 into @c1
    
end
close c1
deallocate c1
GO
--4.删除主键约束
  SET NOCOUNT ON
DECLARE c1 cursor for
    
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    
from sysobjects
    
where xtype = 'PK'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    
begin
        
exec(@c1)
        
fetch next from c1 into @c1
    
end
close c1
deallocate c1
GO

--5.修改排序规则
SET NOCOUNT ON
DECLARE @S NVARCHAR(1000)
DECLARE C CURSOR FOR --不区分大小写
     SELECT 'ALTER TABLE ['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] '
        
+ DATA_TYPE
        
+ CASE WHEN DATA_TYPE IN('TEXT','NTEXT') THEN '' ELSE
              
QUOTENAME(CHARACTER_MAXIMUM_LENGTH,'(')
           
END
        
+ ' COLLATE CHINESE_PRC_CI_AS_WS ' 
        
+ CASE IS_NULLABLE WHEN 'NO' THEN ' NOT NULL ' ELSE '' END
    
FROM INFORMATION_SCHEMA.COLUMNS
    
WHERE DATA_TYPE IN('varchar','nvarchar','char','nchar','text','ntext')
OPEN C
FETCH C INTO @S
WHILE @@FETCH_STATUS=0
BEGIN
    
EXEC(@S)
    
FETCH C INTO @S
END
CLOSE C
DEALLOCATE C
GO
--6.重新创建主键约束(执行导出的脚本)

--7.重新创建外键约束脚本(执行导出的脚本)