[sql]
USE master
go
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
IF OBJECT_ID('sp_table_copy','P') IS NOT NULL
DROP PROC sp_table_copy
go
CREATE PROCEDURE sp_table_copy
(
@table NVARCHAR(128) ,
@table_new NVARCHAR(128),
@is_identity CHAR(1) = 'N'
)
AS
IF OBJECT_ID(@table, 'U') IS NULL
BEGIN
SELECT '不存在表：' + @table AS return_result
RETURN
END
IF @is_identity NOT IN ('Y','N')
BEGIN
SELECT '@is_identity：只能是Y或N' AS return_result
RETURN
END
DECLARE @sql NVARCHAR(MAX)
SET @sql = ' SELECT TOP (0) '
IF @is_identity = 'N'
SET @sql = @sql
+ STUFF(( SELECT ',' + ( CASE WHEN is_identity = 1 THEN ' 0 + ' + name + ' AS ' + name + '' ELSE '' + name + '' END )
FROM sys.columns
WHERE object_id = OBJECT_ID(@table, 'U')
FOR
XML PATH('')
), 1, 1, '') + CHAR(13) + CHAR(10) + ' INTO ' + @table_new + CHAR(13) + CHAR(10) +