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