分享：sp_table_copy 根据旧表创建新表，并去掉IDENTITY属性。

最新推荐文章于 2021-04-22 18:40:18 发布

原创最新推荐文章于 2021-04-22 18:40:18 发布 · 置顶 · 1.2k 阅读

CC 4.0 BY-SA版权

本文介绍了一个SQL脚本，用于创建并复制数据库表，包括检查表是否存在、验证参数、选择列并生成相应的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) + 
                ' FROM ' + @table + ''
                    ELSE  
        SET @sql = @sql
        + STUFF(( SELECT    ',' + '' + name + '' 
                  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) + 
                ' FROM ' + @table + ''
                          
    PRINT @sql 
    IF object_id(@table_new,'U') IS NULL 
    EXEC sp_executesql @sql 
	
GO
EXEC sp_MS_marksystemobject 'sp_table_copy'