“IDENTITY_INSERT is already ON for table X” When Applied to Multiple Tables

Today, I needed to insert data into multiple tables in SQL Server and write to the tables’ identity columns. My SQL script looked like this:

SET IDENTITY_INSERT Table1 ON
SET IDENTITY_INSERT Table2 ON
SET IDENTITY_INSERT Table3 ON

-- insert statements go here

SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table2 OFF
SET IDENTITY_INSERT Table3 OFF

The first time I ran the script, I got the following error at line 2: IDENTITY_INSERT is already ON for table ‘Table1’. Cannot perform SET operation for table ‘Table2’. What’s wrong? Continue reading

Automatically Re-Create Stored Procedures In SQL Server

One day I discovered that the system table sysdepends in my database was not valid. I know that dropping and re-creating a stored procedure will update sysdepends for that single procedure, but what if I had 100s of stored procedures? Of course, there’s a script for that and for all who are too lazy to want to write that script themselves – here it is:

DECLARE  @name			NVARCHAR(128),
         @definition	NVARCHAR(MAX)

DECLARE cur CURSOR FOR
	SELECT		o.name, m.definition
	FROM		sys.sql_modules m 
	INNER JOIN	sys.objects o ON m.object_id = o.object_id
	WHERE		o.type = 'P'

OPEN cur

	FETCH cur INTO @name, @definition

	WHILE @@Fetch_Status = 0
	BEGIN
		EXEC('DROP PROCEDURE ' + @name)
		EXEC(@definition)
		
		FETCH cur INTO @name, @definition
	END

CLOSE cur

DEALLOCATE cur

Listing Objects Used in Stored Procedures (MS SQL)

Recently, I had to clean up a large data base. Some of the tables in that database were obsolete and needed to get deleted. Obsolete tables were not used in any stored procedures, so I needed to list all the tables used in stored procedures and subtract them from the set of all tables in the dataset to get the ones I could delete. So the starting point is to get a list of all objects used in a USP:
Continue reading