2013-04-22存储过程

USE [抚顺电厂]
GO

/****** Object:  StoredProcedure [dbo].[sp_zqt_save]    Script Date: 04/23/2013 15:07:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_zqt_save]
AS
 CREATE TABLE t_zqt_key1(c1 varchar(100), c2 varchar(30), c3 varchar(50))
 CREATE TABLE t_zqt_key2(c1 varchar(100), c2 varchar(30), c3 varchar(50), c4 varchar(30))
 CREATE TABLE t_zqt_key3(c1 varchar(100), c2 varchar(30), c3 varchar(50))
 
 --带主键(key值的包括UNIQUE) 
 INSERT t_zqt_key1
 SELECT CONSTRAINT_NAME, TABLE_NAME,COLUMN_NAME=STUFF((SELECT ','+[COLUMN_NAME] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t WHERE CONSTRAINT_NAME=t1.CONSTRAINT_NAME FOR XML PATH('')), 1, 1, '')
 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t1
 GROUP BY CONSTRAINT_NAME, TABLE_NAME
 
 --带默认约束
 INSERT t_zqt_key2
 SELECT SD.[name] AS "Constraint Name",
  ST.[name] AS "Table Name",
  SC.[name] AS "Column Name",
  SD.definition AS "Default Value" 
 FROM sys.tables ST INNER JOIN
  sys.syscolumns SC ON ST.[object_id] = SC.[id] INNER JOIN
  sys.default_constraints SD
 ON ST.[object_id] = SD.[parent_object_id] AND
  SC.colid = SD.parent_column_id  
 ORDER BY ST.[name], SC.colid
 
 --索引
 INSERT t_zqt_key3
 select t_index.indexName as indexName,t_index.tableName as tableName,
  t_index.columnName as columnName
 from
  (
  select
   i.name as IndexName,
   o.name as TableName,
   ic.key_ordinal as ColumnOrder,
   ic.is_included_column as IsIncluded,
   co.[name] as ColumnName
  from sys.indexes i
   join sys.objects o on i.object_id = o.object_id
   join sys.index_columns ic on ic.object_id = i.object_id
   and ic.index_id = i.index_id
   join sys.columns co on co.object_id = i.object_id
   and co.column_id = ic.column_id
  where i.[type] = 2
   --and i.is_unique = 0
   and i.is_primary_key = 0
   and o.[type] = 'U'
   and ic.is_included_column = 0
   --order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
  ) as t_index
 order by t_index.indexName,t_index.tableName,t_index.columnName
 
 --过滤系统表
 delete t_zqt_key1 where c2 = 'dtproperties' or c2 = 'sysdiagrams'
 delete t_zqt_key2 where c2 = 'dtproperties' or c2 = 'sysdiagrams'
 delete t_zqt_key3 where c2 = 'dtproperties' or c2 = 'sysdiagrams'
 
    --定义变量
 declare @c1 varchar(100)
 declare @c2 varchar(30)
 declare @c3 varchar(50) 
 declare @c4 varchar(30)
 declare @ls_sql varchar(max)
 
 --删除索引
 declare cur_key4 cursor scroll for
  select c1,c2,
   c3 = STUFF((
    SELECT ','+[c3]
    FROM t_zqt_key3 t
    WHERE c1=t1.c1 FOR XML PATH('')
     ), 1, 1, '')
  FROM t_zqt_key3 t1
  GROUP BY c1,c2
 open cur_key4
  fetch next from cur_key4 into @c1,@c2,@c3
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'DROP INDEX ' + @c1 + ' ON ' + @c2
   exec(@ls_sql)
   fetch cur_key4 into @c1,@c2,@c3
  end
 close cur_key4
 
 --删除Key 
 declare cur_key1 cursor scroll for
  select c1,c2,c3 from t_zqt_key1
 open cur_key1
  fetch next from cur_key1 into @c1,@c2,@c3
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'ALTER TABLE ' + @c2 + ' DROP CONSTRAINT ' + @c1
   exec(@ls_sql)
   fetch cur_key1 into @c1,@c2,@c3
  end
 close cur_key1
  
 --删除约束
 declare cur_key2 cursor scroll for
  select c1,c2,c3,c4 from t_zqt_key2
 open cur_key2
  fetch next from cur_key2 into @c1,@c2,@c3,@c4
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'ALTER TABLE ' + @c2 + ' drop constraint ' + @c1
   exec(@ls_sql)
   fetch cur_key2 into @c1,@c2,@c3,@c4
  end
 close cur_key2
 
 --char to varchar
 --定义变量
 declare @t1 varchar(30)
 declare @t2 varchar(30)
 declare @t3 varchar(10)
 declare @t4 varchar(10)
 declare cur_key3 cursor scroll for
  select
   sys.tables.name,
   sys.columns.name,
   sys.columns.max_length,
   case is_nullable
    when 0
     then 'not null'
    else
     ''
   end
  from sys.columns,sys.tables
  where sys.columns.object_id =  sys.tables.object_id and
   user_type_id = 175 and
   max_length <> 1
  order by sys.tables.name
 open cur_key3
  fetch next from cur_key3 into @t1,@t2,@t3,@t4
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'ALTER TABLE ' + @t1 + ' ALTER COLUMN ' + @t2 + ' varchar(' + @t3 + ') ' + @t4
   exec(@ls_sql)
   set @ls_sql = 'UPDATE ' + @t1 + ' SET ' + @t2 + '=RTRIM(' + @t2 + ')'
   exec(@ls_sql)
   fetch cur_key3 into @t1,@t2,@t3,@t4
  end
 close cur_key3
 
 --恢复Key键
 open cur_key1
  fetch next from cur_key1 into @c1,@c2,@c3
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'ALTER TABLE ' + @c2 + ' ADD CONSTRAINT ' + @c1 + ' PRIMARY KEY (' + @c3 + ')'
   exec(@ls_sql)
   fetch cur_key1 into @c1,@c2,@c3
  end
 close cur_key1

 --恢复约束
 open cur_key2
  fetch next from cur_key2 into @c1,@c2,@c3,@c4
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'ALTER TABLE ' + @c2 + ' ADD CONSTRAINT ' + @c1 + ' DEFAULT ' + @c4 + ' FOR ' + @c3
   exec(@ls_sql)
   fetch cur_key2 into @c1,@c2,@c3,@c4
  end
 close cur_key2
 
 --恢复索引
 open cur_key4
  fetch next from cur_key4 into @c1,@c2,@c3
  WHILE @@FETCH_STATUS = 0
  begin
   set @ls_sql = 'CREATE INDEX ' + @c1 + ' ON ' + @c2 + ' (' + @c3 + ')' 
   exec(@ls_sql)
   fetch cur_key4 into @c1,@c2,@c3
  end
 close cur_key4

 --删除临时表
 declare @name varchar(20)
 while(exists(select * from sysobjects where name like 't_zqt_key%'))
 begin
  select @name=name from sysobjects where name like 't_zqt_key%'
  exec ('DROP TABLE '+@name)
 end
 
 --删除游标
 deallocate cur_key1 
 deallocate cur_key2
 deallocate cur_key3
 deallocate cur_key4
 
GO