how to write the delete and reseed for stored procedure?

Question

hi, I have one database for eg 300 table ,i have delete specify the 200 table data only and then reseed identity the delete table . but error withshown . DECLARE @DeleteTableStatement nvarchar( max ) DECLARE C2 CURSOR READ_ONLY FOR SELECT 'Delete From [dbo].[' + TABLE_NAME + ']' from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' and TABLE_NAME NOT IN ( 'xxx' , 'aaa' , 'bbb' , etc ) -- Change your schema appropriately. OPEN C2 FETCH NEXT FROM C2 INTO @DeleteTableStatement WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Executing ' + @DeleteTableStatement execute sp_executesql @DeleteTableStatement DBCC CHECKIDENT (@DeleteTableStatement, RESEED,1); FETCH NEXT FROM C2 INTO @DeleteTableStatement END CLOSE C2 DEALLOCATE C2

Shahriar Nikkhah · Answer

Nitin has the right answer, it's prefect Maybe if you like to go more dynamic you can use... DECLARE @dynamicSQL nvarchar( max ) = '' SELECT @dynamicSQL = @dynamicSQL + N 'TRUNCATE TABLE [' + [TABLE_SCHEMA] + N '].[' + [TABLE_NAME] + N '] ' + CHAR (13) + 'GO' + CHAR (13) + ';' + CHAR (13) + '' FROM INFORMATION_SCHEMA.TABLES --WHERE ....... PRINT @dynamicSQL EXECUTE sp_executeSQL @dynamicSQL

Nitin Sontakke · Answer

Furthermore, if it is Microsoft SQL Server, there is no need to use cursor. Instead store all your dynamic SQL in a variable and execute it later. For example, declare @dynamicSQL nvarchar( max ) = '' select @dynamicSQL += 'truncate table [dbo].[' + name + '] ' from sys.tables where 1 = 1 and ..... execute sp_executeSQL @dynamicSQL Better this way, I believe.

Shahriar Nikkhah · Answer

Make it simple if you can TRUNCATE a table it will get automatically reseeded from what it was nik