Dynamically create table results from non-query

Have you ever executed a command with a result set but you didn’t know the exact schema being returned? Have you ever wanted to create a temporary table from a query but don’t want to look up all of the column data types? Of course you have! T-SQL’s SELECT … INTO makes creating tables from a result set easy. But what about commands with result sets which are not SELECT statements? In that case, we are left with manually building these tables ourselves.

Maybe you’ve returned the file list from a backup file so that you could dynamically create the necessary MOVE statements for your RESTORE command.

DECLARE @FileList TABLE
(
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      Type char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
);

INSERT INTO @fileList
RESTORE FILELISTONLY FROM DISK = 'C:\foo\backupfile.bak';

Then you stepped it up notch and automated the restores by searching a directory.

CREATE TABLE #FileInfo(
ID INT IDENTITY(1, 1),
Name VARCHAR(200),
ParentId INT,
Depth INT,
ISFILE BIT)

DECLARE @Directory VARCHAR(8000) = 'C:\foo'

INSERT INTO #FileInfo(Name, Depth, ISFILE)
 EXEC xp_dirtree @Directory, 0, 1

Man that was a lot of code. What happens if Microsoft decides to change the data types or results for those commands? Will you need to maintain multiple versions of your temporary tables with a server version check thrown in?

Nah… this is one more place where PowerShell is awesome.

Import-Module SQLPS -DisableNameChecking;

$table = Invoke-Sqlcmd -ServerInstance localhost\sql2014_2 -Database BackupTest -Query "RESTORE FILELISTONLY FROM DISK = 'C:\Users\Derik\Desktop\project\sqlHammer\DB Backups\AdventureWorks2012_FULL_1.bak'";

$table | Format-Table -AutoSize

powershell-format-table-results

It is as simple as that. Import the SQLPS module, execute the query, and a table is returned without any need to define it in advance.

Leave a Reply

%d bloggers like this: