1.结构复制：
下面的存储过程是将某数据库中的所有用户表的字段提取出来，去掉重复字段，然后用筛选后的字段创建一张新表。
ALTER PROCEDURE [dbo].[UP_CreateTemp]
as
begin
--1.判断表是否存在
IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))
begin
--2.如果表不存在，创建表
CREATE TABLE [dbo].[Temp](
[TempID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Temp] PRIMARY KEY CLUSTERED
(
[TempID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
end
--
declare @ColumnCount int
--3.判断临时表是否存在，注意：临时表都存放在[系统数据库的：tempdb数据库]中
if(object_id('tempdb..#TempColumns ') is not null)
begin
drop table #TempColumns
end
--4.创建自增长临时表
SELECT IDENTITY(INT,1,1) AS [NewID], * into #TempColumns
--5.利用系统视图
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA ='dbo'
AND
EXISTS (
SELECT Table_Name
FROM INFORMATION_SCHEMA.TABLES T
WHERE C.Table_Name = T.Table_Name
AND T.Table_Name<>'sysdiagrams'
)
--
select @ColumnCount=count(table_catalog) from #TempColumns
print @ColumnCount
--
declare @script nvarchar(255);
declare @ColumnName nvarchar(255);
declare @ColumnType nvarchar(255);
declare @ColumnMax nvarchar(255);
declare @ColumnNullable nvarchar(255);
--6.循环
while @ColumnCount>0
begin
if not exists(
select * from #TempColumns as T where T.[NewID]=@ColumnCount and
exists(
select * from INFORMATION_SCHEMA.COLUMNS C
where c.Table_name='temp'and c.Column_Name=T.Column_Name
)
)
begin
select @ColumnName=T.Column_Name, @ColumnType=Data_Type,@ColumnMax=character_Maximum_Length, @ColumnNullable=IS_Nullable from #TempColumns as T where T.[newid]=@ColumnCount
if @ColumnType='nvarchar'
begin
set @ColumnType=@ColumnType+'('+@ColumnMax+')';
end
if @ColumnNullable='yes'
begin
set @ColumnNullable='null';
end
else
begin
set @ColumnNullable='not null';
end
set @script=' ';
set @script=@script+'alter table [dbo].[Temp]';
set @script=@script+' add '+' '+@ColumnName+' '+@ColumnType+' '+@ColumnNullable;
--7.执行拼接的语句，注意：exec(@script)，而exec @script 将会出错
exec(@script)
print @ColumnCount;
end
set @ColumnCount=@ColumnCount-1;
end
end
2.数据复制：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[UP_CopyTableFromView]
(
@sourceViewName nvarchar(50),
@newTableName nvarchar(50),
@copySuccess bit output
)
as
begin
declare @script nvarchar(500);
--判断视图是否存在
IF not EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID('[dbo].['+@sourceViewName+']') AND type in (N'V'))
begin
set @copySuccess=2;
return ;
end
--判断表是否存在
IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].['+@newTableName+']') AND type in (N'U'))
begin
begin try
set @script='select * into '+ isnull(@newTableName,'')+' from '+isnull(@sourceViewName,'');
print @script;
begin tran
exec (@script);
commit tran
--
set @copySuccess=0;
end try
begin catch
set @copySuccess=1;
end catch
end
end