如何创建一个通用脚本来删除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?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何创建一个通用脚本来删除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屋!
查看全文