Karl’s Blog
msgbartop
msgbarbottom

10 Nov 17 SQL Script to export all data in a database as INSERT statements

This script will export all data from a Microsoft SQL Server database as a script of INSERT statements.

SET NOCOUNT ON

DECLARE @SQL VARCHAR(MAX)

SET @SQL = null

SELECT @SQL = COALESCE(@SQL + '
UNION ALL
', '') + 'SELECT ''' + t.TABLE_SCHEMA + ''' AS TABLE_SCHEMA, ''' + t.TABLE_NAME + ''' AS TABLE_NAME, COUNT(*) AS records FROM [' + t.TABLE_SCHEMA + '].[' + t.table_name + ']'
FROM information_schema.tables as t
WHERE t.table_type = 'BASE TABLE'
  AND t.table_name NOT IN ('dtproperties', 'DB_VERSION', 'SCHEMA_UPDATER_VERSIONING')

CREATE TABLE #tmp_tables (
    TABLE_SCHEMA VARCHAR(50)
  , TABLE_NAME VARCHAR(50)
)

EXEC('
INSERT INTO #tmp_tables (TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM (
' + @SQL + '
) t
WHERE records > 0')

DECLARE tables_cursor cursor 
FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM #tmp_tables
FOR READ ONLY;

OPEN tables_cursor

CREATE TABLE #tmp_statement (
    [TABLE_NAME] VARCHAR(50) PRIMARY KEY
  , [STATEMENT] VARCHAR(MAX)
)

PRINT 'DECLARE @maxId INT'
PRINT 'EXEC sp_msforeachtable ''ALTER TABLE ? NOCHECK CONSTRAINT all'''
PRINT ''

DECLARE @tableSchema VARCHAR(50)
  , @tableName VARCHAR(50)
  , @identityColumn VARCHAR(50)

FETCH NEXT FROM tables_cursor
INTO @tableSchema, @tableName

DECLARE @selectSQL VARCHAR(MAX)

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @SQL = NULL
  SET @selectSQL = NULL

  SELECT @SQL = COALESCE(@SQL + ', ', '') + '[' + c.COLUMN_NAME +']'
    , @selectSQL = COALESCE(@selectSQL + ', ', ' + ''SELECT ') + 
      CASE 
        WHEN c.DATA_TYPE IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext') THEN ''' + COALESCE('''''''' + REPLACE([' + c.COLUMN_NAME +'], '''''''', '''''''''''') + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money') THEN ''' + COALESCE(CONVERT(VARCHAR, [' + c.COLUMN_NAME +']), ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('float', 'real') THEN ''' + COALESCE(CONVERT(VARCHAR(50), [' + c.COLUMN_NAME +'], 3), ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('date', 'datetimeoffset', 'datetime2', 'smalldatetime', 'datetime', 'time') THEN ''' + COALESCE('''''''' + CONVERT(VARCHAR(36), [' + c.COLUMN_NAME +']) + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('uniqueidentifier') THEN ''' + COALESCE('''''''' + CONVERT(VARCHAR(36), [' + c.COLUMN_NAME +']) + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('binary', 'varbinary', 'image') THEN ''' + COALESCE(CONVERT(VARCHAR, [' + c.COLUMN_NAME +']), ''NULL'') + '''
        ELSE 'ERROR'
      END
  FROM information_schema.columns c
  WHERE c.table_name = @tableName
  ORDER BY c.table_name, c.ORDINAL_POSITION

  INSERT INTO #tmp_statement ([TABLE_NAME], [STATEMENT])
  SELECT @tableName, 'INSERT INTO [' + @tableSchema + '].[' + @tableName + '] (' + @SQL + ')
'

  SET @SQL = 'DECLARE @SQL VARCHAR(MAX)
  
  SELECT @SQL = COALESCE(@SQL + ''
UNION ALL
'', '''')' + @selectSQL + ''' FROM [' + @tableName +']

  UPDATE s
  SET [statement] = [statement] + @SQL
  FROM #tmp_statement s
  WHERE s.TABLE_NAME = ''' + @tableName + '''
'

  --PRINT @SQL
  EXEC (@SQL)

  SET @identityColumn = NULL
  SELECT @identityColumn = c.COLUMN_NAME FROM information_schema.columns c WHERE TABLE_SCHEMA = @tableSchema AND TABLE_NAME = @tableName AND COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') = 1

  IF (@identityColumn IS NOT NULL)
    PRINT 'SET IDENTITY_INSERT [' + @tableSchema + '].[' + @tableName + '] ON'

  SELECT @SQL = [STATEMENT] FROM #tmp_statement WHERE TABLE_NAME = @tableName
  PRINT @SQL

  IF (@identityColumn IS NOT NULL)
  BEGIN
    PRINT 'SET IDENTITY_INSERT [' + @tableSchema + '].[' + @tableName + '] OFF'
    PRINT 'SELECT @maxId = MAX([' + @identityColumn + ']) FROM [' + @tableSchema + '].[' + @tableName + ']'
    PRINT 'DBCC CHECKIDENT ([' + @tableName + '], RESEED, @maxId)'
  END

  PRINT ''

  FETCH NEXT FROM tables_cursor
  INTO @tableSchema, @tableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

DROP TABLE #tmp_statement
DROP TABLE #tmp_tables

PRINT 'EXEC sp_msforeachtable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''
PRINT 'GO'
PRINT ''

10 Apr 12 How to reindex every table with SQL Server

A useful script I’ve used to reindex every table in an SQL Server database.

EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '' '', 80)'
GO
EXEC sp_updatestats
GO