Uncategorized

Drop all tables in schema (WHILE mode)

A simple script to drop all tables from the selected schema. You can adjust the metadata query simply and delete tables based on your own filter. Implement the CATCH block bases on your needs. SET NOCOUNT ON DECLARE @TableName NVARCHAR(300) DECLARE @Stmt NVARCHAR(MAX) DECLARE @TablesToBeDeleted TABLE ( TableName NVARCHAR(300) NOT…

Read more
Uncategorized

Backup single table with timestamp

Simple script to create backup of given table in this format: SELECT * INTO [dbo].[SampleTable_20180820152224] FROM [dbo].[SampleTable]; DECLARE @TimeStamp NVARCHAR(MAX) DECLARE @Stmt NVARCHAR(MAX) DECLARE @SchemaName NVARCHAR(128) DECLARE @TableName NVARCHAR(128) SET @SchemaName = ‘dbo’ SET @TableName = ‘SampleTable’ SET @TimeStamp = FORMAT(GETDATE(), ‘yyyyMMddHHmmss’) SET @Stmt = N’SELECT * INTO [‘ +…
Uncategorized

Drop all tables in database

DECLARE @Stmt NVARCHAR(MAX) SET @Stmt = ” SELECT @Stmt = @Stmt + ‘DROP TABLE [‘ + s.name + ‘].[‘ + v.name + ‘];’ FROM sys.tables v INNER JOIN sys.schemas s ON s.schema_id = v.schema_id EXECUTE (@Stmt) GO SELECT * FROM sys.tables GO
Uncategorized

Reset IDENTITY_INSERT to OFF for all tables

DECLARE @Stmt NVARCHAR(MAX) SET @Stmt = CAST( (SELECT ‘SET IDENTITY_INSERT ‘ + QUOTENAME(OBJECT_SCHEMA_NAME([t].[object_id])) + ‘.’ + QUOTENAME([t].[name]) + ‘ OFF’ + CHAR(10) FROM [sys].[columns] [c] JOIN [sys].[tables] [t] ON [t].[object_id] = [c].[object_id] WHERE [c].[is_identity] = 1 ORDER BY 1 FOR XML PATH(”)) AS NVARCHAR(MAX) ) PRINT @Stmt GO

Read more
Uncategorized

Reset IDENTITY_INSERT setting for session in case it is ON for unknown table

DECLARE @Cmd NVARCHAR(MAX) SET @Cmd = CAST(( SELECT ‘SET IDENTITY_INSERT ‘ + QUOTENAME(OBJECT_SCHEMA_NAME([t].[object_id])) + ‘.’ + QUOTENAME([t].[name]) + ‘ OFF;’ + CHAR(10) FROM [sys].[columns] [c] INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [c].[object_id] WHERE [c].[is_identity] = 1 ORDER BY 1 FOR XML PATH(”) ) AS NVARCHAR(MAX)); PRINT @Cmd EXEC [sys].[sp_executesql]…