Uncategorized

Backup single table with timestamp

Simple script to create backup of given table in this format: Transact-SQL SELECT * INTO [dbo].[SampleTable_20180820152224] FROM [dbo].[SampleTable]; 1 SELECT * INTO [dbo].[SampleTable_20180820152224] FROM [dbo].[SampleTable]; Transact-SQL 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 [' + @SchemaName + '].[' + @TableName + '_' + @TimeStamp + '] FROM [' + @SchemaName + '].[' + @TableName + '];' PRINT @Stmt EXECUTE(@Stmt) GO 123456789101112131415 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 [' + @SchemaName + '].[' + @TableName + '_' + @TimeStamp + '] FROM [' + @SchemaName + '].[' + @TableName + '];' PRINT @StmtEXECUTE(@Stmt)GO      
Uncategorized

Drop all tables in database

Transact-SQL 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 12345678910111213 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.tablesGO
Uncategorized

Reset IDENTITY_INSERT to OFF for all tables

Transact-SQL 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 123456789101112131415 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 @StmtGO
Uncategorized

List all Tables with IDENTITY column exists information

Transact-SQL SELECT sch.[name] SchemaName, t.name TableName, OBJECTPROPERTY(t.[object_id], 'TableHasIdentity') TableHasIdentity FROM sys.[tables] t INNER JOIN [sys].[schemas] sch ON [sch].[schema_id] = [t].[schema_id] ORDER BY sch.name, t.name 1234567 SELECT sch.[name] SchemaName, t.name TableName, OBJECTPROPERTY(t.[object_id], 'TableHasIdentity') TableHasIdentityFROM sys.[tables] t INNER JOIN [sys].[schemas] sch ON [sch].[schema_id] = [t].[schema_id]ORDER BY sch.name, t.name  
Uncategorized

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

Transact-SQL 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] @Cmd GO 123456789101112131415161718 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] @CmdGO  
Uncategorized

List of Tables with Number of Rows Including Data and Indexes Size

Transact-SQL SELECT [a3].[name] AS [schemaname], [a2].[name] AS [tablename], [a1].[rows] AS [row_count], ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) * 8 AS [reserved], [a1].[data] * 8 AS [data], ( CASE WHEN ( [a1].[used] + ISNULL([a4].[used], 0) ) > [a1].[data] THEN ( [a1].[used] + ISNULL([a4].[used], 0) ) - [a1].[data] ELSE 0 END ) * 8 AS [index_size], ( CASE WHEN ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) > [a1].[used] THEN ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) - [a1].[used] ELSE 0 END ) * 8 AS [unused] FROM ( SELECT [ps].[object_id], SUM(CASE WHEN ( [ps].[index_id] < 2 ) THEN [ps].[row_count] ELSE 0 END) AS [rows], SUM([ps].[reserved_page_count]) AS [reserved], SUM(CASE WHEN ( [ps].[index_id] < 2 ) THEN ( [ps].[in_row_data_page_count] + [ps].[lob_used_page_count] + [ps].[row_overflow_used_page_count] ) ELSE ( [ps].[lob_used_page_count] + [ps].[row_overflow_used_page_count] ) END) AS [data], SUM([ps].[used_page_count]) AS [used] FROM [sys].[dm_db_partition_stats] [ps] GROUP BY [ps].[object_id] ) AS [a1] LEFT OUTER JOIN ( SELECT [it].[parent_id],SUM([ps].[reserved_page_count]) AS [reserved],SUM([ps].[used_page_count]) AS [used] FROM [sys].[dm_db_partition_stats] [ps] INNER JOIN [sys].[internal_tables] [it] ON ( [it].[object_id] = [ps].[object_id] ) WHERE [it].[internal_type] IN ( 202, 204 ) GROUP BY [it].[parent_id] ) AS [a4] ON ( [a4].[parent_id] = [a1].[object_id] ) INNER JOIN [sys].[all_objects] [a2] ON ( [a1].[object_id] = [a2].[object_id] ) INNER JOIN [sys].[schemas] [a3] ON ( [a2].[schema_id] = [a3].[schema_id] ) WHERE [a2].[type] <> N'S' AND [a2].[type] <> N'IT' ORDER BY [row_count] DESC 123456789101112131415161718192021222324252627 SELECT        [a3].[name] AS [schemaname],        [a2].[name] AS [tablename],        [a1].[rows] AS [row_count],        ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) * 8 AS [reserved],        [a1].[data] * 8 AS [data],        ( CASE WHEN ( [a1].[used] + ISNULL([a4].[used], 0) ) > [a1].[data] THEN ( [a1].[used] + ISNULL([a4].[used], 0) ) - [a1].[data] ELSE 0 END ) * 8 AS [index_size],        ( CASE WHEN ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) > [a1].[used] THEN ( [a1].[reserved] + ISNULL([a4].[reserved], 0) ) - [a1].[used] ELSE 0 END ) * 8 AS [unused]FROM    ( SELECT    [ps].[object_id],                    SUM(CASE WHEN ( [ps].[index_id] < 2 ) THEN [ps].[row_count] ELSE 0 END) AS [rows],                    SUM([ps].[reserved_page_count]) AS [reserved],                    SUM(CASE WHEN ( [ps].[index_id] < 2 ) THEN ( [ps].[in_row_data_page_count] + [ps].[lob_used_page_count] + [ps].[row_overflow_used_page_count] )                             ELSE ( [ps].[lob_used_page_count] + [ps].[row_overflow_used_page_count] ) END) AS [data],                    SUM([ps].[used_page_count]) AS [used]          FROM      [sys].[dm_db_partition_stats] [ps]          GROUP BY  [ps].[object_id]        ) AS [a1]        LEFT OUTER JOIN ( SELECT    [it].[parent_id],SUM([ps].[reserved_page_count]) AS [reserved],SUM([ps].[used_page_count]) AS [used]                          FROM      [sys].[dm_db_partition_stats] [ps]                             INNER JOIN [sys].[internal_tables] [it] ON ( [it].[object_id] = [ps].[object_id] )                          WHERE     [it].[internal_type] IN ( 202, 204 )                          GROUP BY  [it].[parent_id]                        ) AS [a4] ON ( [a4].[parent_id] = [a1].[object_id] )        INNER JOIN [sys].[all_objects] [a2] ON ( [a1].[object_id] = [a2].[object_id] )        INNER JOIN [sys].[schemas] [a3] ON ( [a2].[schema_id] = [a3].[schema_id] )WHERE   [a2].[type] <> N'S' AND [a2].[type] <> N'IT'ORDER BY [row_count] DESC