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 =…
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…
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…