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 ''

09 May 12 How to Create a RAM Disk in Mac OSX

This is a useful example of how to create a RAM disk in Mac OSX. The number 1165430 represents the number of sectors to allocate; each sector contains 512 bytes. The example below will create a RAM disk named ramdisk with 569.06 MB of space.

diskutil erasevolume HFS+ "ramdisk" `hdiutil attach -nomount ram://1165430`

25 Apr 12 How to Hard Reset Samsung 10.1 Tablet

I have the developer version of the tablet given to the attendees of GoogleIO 2011, if you have a comercial version of the tablet these instructions probably do not apply. The model number I’m working with is GT-P7510.

In order to reset the table you will need to download the android sdk; be sure to include the optional platform tools package. The platform package will install the fastboot utility we will use later on.

Step 1 – Place the Tablet into fastboot mode

  1. Power off the tablet
  2. Hold down the power and volume down (left) buttons
  3. When the USB and Download icons appear, choose the USB icon. Volume down button will toogle selection and the volume up will activate the mode.

Your tablet will now be in the fastboot mode.

Step 2 – Factory Reset

  1. Open a terminal
  2. Change directory to android sdk installation folder/platform-tools
  3. Execute fastboot -w

Should only take a few seconds and you’re done.

Tags: , , ,

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