如何创建一个通用脚本来删除Sql server中DB的所有表的列插入OFF？ [英] How to create a Generic Script to remove Identity Insert OFF of column for all tables of a DB in Sql server?

问题描述

如何创建一个通用脚本来删除Sql server中DB的所有表的列插入OFF列？



我正在进行档案自动化过程。我正在将数据从原始数据库迁移到Archival DB。我想删除Archival DB中列的标识插入，并将数据从原始数据库移动到Archival DB。



我有一个代码如下：

  BEGIN   TRANSACTION  
  SET   QUOTED_IDENTIFIER   ON  
  SET   ARITHABORT   ON  
  SET   NUMERIC_ROUNDABORT   OFF  
  SET   CONCAT_NULL_YIELDS_NULL   ON  
  SET   ANSI_NULLS   ON  
  SET   ANSI_PADDING   ON  
  SET   ANSI_WARNINGS   ON  
  COMMIT  
 
  BEGIN   TRANSACTION  
  GO  
 
  CREATE   TABLE  dbo.Tmp_example1 
（ 
 eid  INT   NOT   NULL   IDENTITY （ 1 ， 1 ）
） ON  [ PRIMARY ] 
  GO  
 
  SET   IDENTITY_INSERT  dbo.Tmp_examp le1  ON  
  GO  
 
  IF   EXISTS （ SELECT  *  FROM  dbo.example1）
  EXEC （'  INSERT INTO dbo.Tmp_example1（eid）
 SELECT eid FROM dbo.example1 WITH（HOLDLOCK TABLOCKX）'）
  GO  
 
  SET   IDENTITY_INSERT  dbo.Tmp_example1 关闭 
  GO  
 
  DROP  表 dbo.example1 
  GO  
 
  EXECUTE  sp_rename N '  dbo.Tmp_example1'，N '  example1'，'  OBJECT' 
  GO  
 
  COMMIT  

解决方案

试试这个：

  DECLARE   @ Rows   INT  =  0  
  DECLARE   @SQL   NVARCHAR （MAX）
  DECLARE   @MySQL   AS   TABLE  
（
 ID  int   IDENTITY （ 1 ， 1 ）主要  key   NOT   NULL  
，SQLTEXT  NVARCHAR （MAX）
）
 
  SET   NOCOUNT   ON  
  INSERT   @MySQL  
  SELECT   DISTINCT  '  SET IDENTITY_INSERT' + ST.TABLE_SCHEMA + ' 。' + ST.TABLE_NAME + '  OFF' 
  FROM  INFORMATION_SCHEMA.TABLES  AS  ST 
  INNER   JOIN  IN FORMATION_SCHEMA.COLUMNS  AS  SC 
  ON （ST.TABLE_SCHEMA + ' 。' + ST.TABLE_NAME）=（SC.TABLE_SCHEMA + ' 。' + SC.TABLE_NAME）
  WHERE  ST.TABLE_TYPE = '  BASE TABLE' 
  AND  
 < span class =code-sdkkeyword> COLUMNPROPERTY （ OBJECT_ID （ST.TABLE_SCHEMA + ' 。' + ST.TABLE_NAME）
，SC.COLUMN_NAME，'  IsIdentity'）=  1  
 
  ORDER   BY   1  
 
  SELECT  @ Rows = COUNT（ 1 ） FROM   @MySQL  
 
  WHILE （ @ Rows >  0 ）
  BEGIN  
  SELECT   @ SQL  = SQLTEXT  FROM   @MySQL   WHERE  ID =  @ Rows  
 
  EXECUTE  sp_executesql  @ SQL  
 
  SET  < span class =code-sdkkeyword> @ Rows  =  @ Rows  -1 
 
 结束 

谢谢，



Kuthuparakkal

How to create a Generic Script to remove Identity Insert OFF of column for all tables of a DB in Sql server?

I am doing Archival Automation Process. I am migrating data from Original DB to Archival DB . I want to remove the Identity Insert of the columns in Archival DB and move the data from Original DB to Archival DB.

I have a single code as below:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT

BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_example1
(
    eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO

IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO

DROP TABLE dbo.example1
GO

EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO

COMMIT

解决方案

Try this:

DECLARE @Rows INT = 0
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MySQL AS TABLE
(
    ID int IDENTITY (1, 1) Primary key NOT NULL 
  , SQLTEXT NVARCHAR(MAX)
)

SET NOCOUNT ON
INSERT @MySQL
SELECT DISTINCT 'SET IDENTITY_INSERT ' + ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME + ' OFF'
FROM INFORMATION_SCHEMA.TABLES AS ST
INNER JOIN  INFORMATION_SCHEMA.COLUMNS AS SC
ON (ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME) = (SC.TABLE_SCHEMA +'.'+ SC.TABLE_NAME)
WHERE ST.TABLE_TYPE = 'BASE TABLE'
      AND
	  COLUMNPROPERTY(OBJECT_ID(ST.TABLE_SCHEMA +'.'+ ST.TABLE_NAME)
                      ,SC.COLUMN_NAME,'IsIdentity') = 1

ORDER BY 1

SELECT @Rows=COUNT(1) FROM @MySQL

WHILE(@Rows > 0)
BEGIN
	SELECT @SQL = SQLTEXT FROM @MySQL WHERE ID = @Rows

	EXECUTE sp_executesql @SQL

	SET @Rows = @Rows -1

END

Thanks,

Kuthuparakkal

这篇关于如何创建一个通用脚本来删除Sql server中DB的所有表的列插入OFF？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！