Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Building Reusable Table Build Scripts Using SQL Prompt

DZone's Guide to

Building Reusable Table Build Scripts Using SQL Prompt

Read this article in order to view a tutorial that will explain how to create a table build script by using Prompt's "Script as Insert" feature.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

You've been working on a query, function, or procedure, and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query.

SELECT *
    FROM Sales.Customer
      INNER JOIN Person.Person 
        ON Customer.PersonID = Person.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress
        ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
      INNER JOIN Person.Address
        ON BusinessEntityAddress.AddressID = Address.AddressID
      INNER JOIN Person.AddressType
        ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
Listing 1

You need to store the results of this query, probably using an INSERT statement but first, you need to create a temporary table or table variable. How do you do it? It sounds simple enough; you just need a CREATETABLE or DECLARE...TABLE statement to create a temporary table or table variable and pop the results into it. You try the SQL Prompt column picker ( Ctrl+space). Nope, that just gives you just the column names; to create a temporary table or table variable you also need a list of the types and the nullability ( NULL or NOTNULL) for each column, and they've got to be right, and in the correct order.

And there was you thinking, "I just wanted to get home early today!"

Creating a Table Build Script Using Prompt's "Script as Insert" Feature

Ah, but wait...we can get a table build script using SQL Prompt's versatile "Script as insert" feature! We just want to create the temporary table from the INSERT script that is generated.

Deftly, you check that the result pane is set to 'grid' ( Query | Results to), replace the SELECT * with SELECT TOP1*, and hit execute. Select the entire row in the grid by clicking on the empty top left square of the grid ( row 1 - column 1 cell if you are a spreadsheet freak), then right-click on the selected row(s) to get the context menu and hit 'Script as INSERT'. A new query pane opens with the code to insert the results into a table.

Excellent. You highlight just the CREATETABLE part of the script, and run it.

CREATE TABLE #temptable ( [CustomerID] int, [PersonID] int, [StoreID] int, [TerritoryID] int, [AccountNumber] varchar(10), [rowguid] uniqueidentifier, [ModifiedDate] datetime, [BusinessEntityID] int, [PersonType] nchar(2), [NameStyle] bit, [Title] nvarchar(8), [FirstName] nvarchar(50), [MiddleName] nvarchar(50), [LastName] nvarchar(50), [Suffix] nvarchar(10), [EmailPromotion] int, [AdditionalContactInfo] xml, [Demographics] xml, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [BusinessEntityID] int, [AddressID] int, [AddressTypeID] int, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [AddressID] int, [AddressLine1] nvarchar(60), [AddressLine2] nvarchar(60), [City] nvarchar(30), [StateProvinceID] int, [PostalCode] nvarchar(15), [SpatialLocation] geography, [rowguid] uniqueidentifier, [ModifiedDate] datetime, [AddressTypeID] int, [Name] nvarchar(50), [rowguid] uniqueidentifier, [ModifiedDate] datetime )
Listing 2

Bang!

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'rowguid' in table '#temptable' is specified more than once.

What's gone wrong? The use of SELECT *, which should normally give you a twinge of conscience, was there deliberately in this case, because it makes it more awkward for us. It allows for duplicate column names in the results, and you can't even do a SELECT...INTO with that. There are typed XML columns too, which will also trigger an ' XMLcolumnxxxistypedwithaschemacollection' error, if you are using a temporary table, even if there were no duplicate column names.

In this case, both the rowguid and ModifiedDate column appears five times. Prompt's action menu gives you the option to replace that SELECT* with a list of the columns so that you could then delete the duplicate columns, or you can select just the columns you want from the column-picker. Useful in most circumstances, but not quite what you need here because it would no longer be SELECT *. No, the destination table needs unique column names and you've also still got the problem of the XML typed schema collections. Bear with me: I'm just imagining the horror. It may be the result thrown at you from a stored procedure that you can't alter, or a wild view. Our query is just an example.

Doing It by Hand: A Temporary Batch to Create the Column List

Okay, maybe you can still get home in time if you are on SQL Server 2012 or later, but you'll need to do it the harder way and create some code to fix duplicate names by giving each of the five rowguid columns a unique number (the column position). As quick as a flash, you produce this.

/**
  Summary: >
    This is a temporary batch for creating such things as table variable,
    temporary tables or anything else that needs a column list 
  Author: Phil Factor
  **/
  Declare @TheExpression NVARCHAR(MAX)=
  N'SELECT *
      FROM Sales.Customer
        INNER JOIN Person.Person 
          ON Customer.PersonID = Person.BusinessEntityID
        INNER JOIN Person.BusinessEntityAddress
          ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
        INNER JOIN Person.Address
          ON BusinessEntityAddress.AddressID = Address.AddressID
        INNER JOIN Person.AddressType
          ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;'
  SELECT 'DECLARE @MyTableVariable table ('+
    Stuff ((SELECT ',
      '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)
       + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'
     --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END
     AS ThePath
    FROM sys.dm_exec_describe_first_result_set
    (@TheExpression, NULL, 0) AS f --(@tsql, @Params, @include_browse_information
    -- use  sys.sp_describe_first_result_set for a batch
    LEFT OUTER JOIN 
      (SELECT name FROM sys.dm_exec_describe_first_result_set
        (@TheExpression, NULL, 0) AS f 
       GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames
    ON DetectDuplicateNames.name=f.name
  ORDER BY column_ordinal
  FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')'
Listing 3

This script uses the sys.dm_exec_describe_first_result_set dynamic management function (or the similar sys.sp_describe_first_result_set for a batch), which allows you to get the metadata from a range of objects. You provide the expression containing the T-SQL statements, or batch, and a description of any embedded parameters, and it returns the names of the columns, their ordinal position, nullability, data types and more. We want more than just base table; we want to know what is returned by a query on any combination of table-sources or the first result from a batch. We also want to know what is returned by procedures or triggers when you execute them.

I use sys.dm_exec_describe_first_result_set twice, first to test for duplicate names, and then to get a column definition for each column. I then glue each column definition together into a table build script, using the XML concatenation trick.

Let's give this a try. In SSMS, if you want the build script formatted with line breaks, make sure that you've set Query | Resultsto | Results to Text, and you've allowed a nice long value for Query | Query Options | Results | Text | Maximum number of characters displayed in each column (8192 will suffice). We execute the code and get this in the result pane of SSMS.

DECLARE @MyTableVariable table (
      CustomerID int NOT NULL,
      PersonID int NULL,
      StoreID int NULL,
      TerritoryID int NULL,
      AccountNumber varchar(10) NOT NULL,
      rowguid_6 uniqueidentifier NOT NULL,
      ModifiedDate_7 datetime NOT NULL,
      BusinessEntityID_8 int NOT NULL,
      PersonType nchar(2) NOT NULL,
      NameStyle bit NOT NULL,
      Title nvarchar(8) NULL,
      FirstName nvarchar(50) NOT NULL,
      MiddleName nvarchar(50) NULL,
      LastName nvarchar(50) NOT NULL,
      Suffix nvarchar(10) NULL,
      EmailPromotion int NOT NULL,
      AdditionalContactInfo xml NULL,
      Demographics xml NULL,
      rowguid_19 uniqueidentifier NOT NULL,
      ModifiedDate_20 datetime NOT NULL,
      BusinessEntityID_21 int NOT NULL,
      AddressID_22 int NOT NULL,
      AddressTypeID_23 int NOT NULL,
      rowguid_24 uniqueidentifier NOT NULL,
      ModifiedDate_25 datetime NOT NULL,
      AddressID_26 int NOT NULL,
      AddressLine1 nvarchar(60) NOT NULL,
      AddressLine2 nvarchar(60) NULL,
      City nvarchar(30) NOT NULL,
      StateProvinceID int NOT NULL,
      PostalCode nvarchar(15) NOT NULL,
      SpatialLocation geography NULL,
      rowguid_33 uniqueidentifier NOT NULL,
      ModifiedDate_34 datetime NOT NULL,
      AddressTypeID_35 int NOT NULL,
      Name nvarchar(50) NOT NULL,
      rowguid_37 uniqueidentifier NOT NULL,
      ModifiedDate_38 datetime NOT NULL)
Listing 4

It is a lot, but then we are using an extreme example just to prove the concept. Append Listing 5 directly to the end of the table variable creation script in Listing 4, and try it out.

…(Listing 4 here)…
  INSERT INTO @MyTableVariable
    SELECT * FROM 
     Sales.Customer
      INNER JOIN Person.Person 
        ON Customer.PersonID = Person.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress
        ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
      INNER JOIN Person.Address
        ON BusinessEntityAddress.AddressID = Address.AddressID
      INNER JOIN Person.AddressType
        ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
Listing 5

And if all is well, it comes back with is (18508 rows affected).

As you have SQL Prompt, you would look around furtively, highlight that * and hit the TAB key to expand the wildcard into individual column names (or use the Expand Wildcards action from the Action list), just so it all looks nice. You'll end up with:

INSERT INTO @MyTableVariable
   SELECT Customer.CustomerID, Customer.PersonID, Customer.StoreID,
         Customer.TerritoryID, Customer.AccountNumber, Customer.rowguid,
         Customer.ModifiedDate, Person.BusinessEntityID, Person.PersonType,
         Person.NameStyle, Person.Title, Person.FirstName, Person.MiddleName,
         Person.LastName, Person.Suffix, Person.EmailPromotion,
         Person.AdditionalContactInfo, Person.Demographics, Person.rowguid,
         Person.ModifiedDate, BusinessEntityAddress.BusinessEntityID,
         BusinessEntityAddress.AddressID, BusinessEntityAddress.AddressTypeID,
         BusinessEntityAddress.rowguid, BusinessEntityAddress.ModifiedDate,
         Address.AddressID, Address.AddressLine1, Address.AddressLine2, Address.City,
         Address.StateProvinceID, Address.PostalCode, Address.SpatialLocation,
         Address.rowguid, Address.ModifiedDate, AddressType.AddressTypeID,
         AddressType.Name, AddressType.rowguid, AddressType.ModifiedDate
    FROM Sales.Customer
      INNER JOIN Person.Person 
        ON Customer.PersonID = Person.BusinessEntityID
      INNER JOIN Person.BusinessEntityAddress
        ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
      INNER JOIN Person.Address
        ON BusinessEntityAddress.AddressID = Address.AddressID
      INNER JOIN Person.AddressType
        ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
Listing 6

Even though you haven't aliased the duplicate names, you won't have duplicate names in the destination table, so everything will work. In this particular case, you could, of course, have used the column picker to get just the columns you really need, but in the sort of scenario we're imagining, you can't fix a routine that is throwing you this result.

Okay, you may get home early after all.

Creating a Helper Routine Using a SQL Prompt Snippet

You may be looking back at that phrase earlier in the article, and asking yourself "What did he mean by 'quick as a flash?" Well, because my memory is splendid, but only for remembering such things as where the jar with the chocolate biscuits is, I'd already saved the code for removing column duplicates as a "helper" routine in my SQL Prompt snippet library.

Here is the code to create the snippet.

/**
  Summary: >
    This is a temporary batch for creating such things as table variables,
    temporary tables or anything else that needs a column list 
  Author: Phil Factor
  **/
  Declare @TheExpression NVARCHAR(MAX)=
  N'$SELECTEDTEXT$'
  SELECT 'DECLARE @$NameOfVariable$ table ('+
    Stuff ((SELECT ',
      '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)
       + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'
     --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END
     AS ThePath
    FROM sys.dm_exec_describe_first_result_set
    (@TheExpression, NULL, 0) AS f --(@tsql, @Params, @include_browse_information
    -- use  sys.sp_describe_first_result_set for a batch
    LEFT OUTER JOIN 
      (SELECT name FROM sys.dm_exec_describe_first_result_set
        (@TheExpression, NULL, 0) AS f 
       GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames
    ON DetectDuplicateNames.name=f.name
  ORDER BY column_ordinal
  FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')';
Listing 7

In SSMS highlight the code in listing 7, right-click and choose Create Snippet. Give the snippet a name (I used tvc, standing for Table Variable Creator) and description, and hit Save. I've already explained all about how to use Prompt Snippets in another article, so see Templates and Snippets in SSMS and SQL Prompt for a full description.

You should add a good default value for the placeholder, @NameOfVariable, which conforms to your house style.

Now all you need to do is highlight Listing 1, find the snippet in the Prompt Action list (start typing its name) and click on it. The result will be the table variable declaration in Listing 3.

If you want to create a base table or a temporary table suitable for receiving the output of any batch, procedure or query, all you need to do is to edit the DECLARE@$NameOfVariable$table to CREATETABLE#NameOfTable for a temporary table or CREATETABLEschema.NameOfTable for an ordinary table.

Let's just try it once more, this time selecting a procedure. If you want the DECLARE script nicely formatted, make sure that the SSMS results pane is set for text rather than grid and that you are set to receive plenty of text (see above). Otherwise, it is fine to select the result from the grid.

In SSMS, tap in the code to execute the uspGetManagerEmployees stored procedure (but without the EXEC statement in this example because it is the start of a batch, e.g. uspGetManagerEmployees 1, which works well with AdventureWorks2012).

Highlight this code and invoke your snippet. You should see this:

/**
  Summary: >
    This is a temporary batch  for creating such things as table variable,
    temporary tables or anything else that needs a column list 
  Author: Phil Factor
  **/
  Declare @TheExpression NVARCHAR(MAX)=
  N'uspGetManagerEmployees 1'
  SELECT 'DECLARE @MyTableVariable table ('+
    Stuff ((SELECT ',
      '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)
       + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'
     --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END
     AS ThePath
    FROM sys.dm_exec_describe_first_result_set
    (@TheExpression, NULL, 0) AS f --(@tsql, @Params, @include_browse_information
    -- use  sys.sp_describe_first_result_set for a batch
    LEFT OUTER JOIN 
      (SELECT name FROM sys.dm_exec_describe_first_result_set
        (@TheExpression, NULL, 0) AS f 
       GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames
    ON DetectDuplicateNames.name=f.name
  ORDER BY column_ordinal
  FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')'
Listing 8

Execute it and you will get the result:

DECLARE @MyTableVariable TABLE
    (
    RecursionLevel INT NULL,
    OrganizationNode NVARCHAR(4000) NULL,
    ManagerFirstName NVARCHAR(50) NOT NULL,
    ManagerLastName NVARCHAR(50) NOT NULL,
    BusinessEntityID INT NULL,
    FirstName NVARCHAR(50) NULL,
    LastName NVARCHAR(50) NULL
    )

So, you just add this in the same batch:

INSERT INTO @MyTableVariable
  EXECUTE uspGetManagerEmployees 1
  SELECT * FROM @MyTableVariable --just to check!
Listing 9

Success!

So, clean up by deleting the batch you created to give you the table build script (highlighted in the screenshot below, just as I am about to hit the delete key) and you can now slip home early!

Conclusion

The SQL Prompt will do most of the mindless tasks that you'd otherwise be obliged to undertake by hand, such as reformatting code or generating lists of column-names. This will hopefully leave more time for the more demanding and interesting jobs.

The SQL Prompt snippets provide a great way of using your favorite time-saving routines as snippets. In this case, a good table-build generator will make things easier and more accurate for your work, especially if you use a lot of working tables in your code. You can, of course, very quickly turn it into a table-valued function to put in your utilities directory, but to have it as a snippet means that you can just delete the generated code when you've used it. The choice is yours!

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
database ,sql prompt

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}