Get column creation datatype for declaration

This is quick way to get a string containing the sql datatype required for a column declaration, a create table, a print variables, or convert data to XML and XML to data, based on temp table and column information.

How to use:

1. Create your select statement 2. Define if you use or not prefix - you have prefix to column names and variable names. 3. Select and run the template to create your snippet code.

ex:

My select: select top 100 * into #temp FROM [MY_TABLES_VIEWS]

Set prefix to variables : v_ Set prefix to columnes : c_

Create my data with temp table

1
2-- Delete temp table
3BEGIN TRY drop table #temp; END TRY BEGIN CATCH END CATCH;
4go
5select * into #temp FROM (select top 100 * from myViewAndTable) as tb1
6DECLARE @prefix_var nvarchar(50) = 'v_';
7DECLARE @prefix_col nvarchar(50) = 'c_';

List my variables, columns, types, nulls

 1
 2--- List my variables, columns, types, nulls
 3SET NOCOUNT ON;
 4SELECT distinct
 5    '@'+ @prefix_var + REPLACE(c.name,' ','_') AS [-- Variables],
 6    '['+ @prefix_col + c.name + ']' as [-- Columns],
 7    t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END AS [-- Types],
 8    isnull(CASE WHEN c.is_nullable = 0 THEN ' NOT ' ELSE NULL END, ' ') + 'NULL' AS [-- Nulls]
 9FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
10WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Select values

 1
 2--- Select values
 3SET NOCOUNT ON;
 4SELECT
 5    CASE c.column_id WHEN 1 THEN '  ' ELSE ', ' END
 6    + '@'+ @prefix_var + REPLACE(c.name,' ','_') + ' = ['+ @prefix_col + c.name + '] '
 7 as [-- Select to value]
 8FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
 9WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
10ORDER BY column_id

Print variables

1
2--- Print variables
3SET NOCOUNT ON;
4SELECT
5    'PRINT ''-- @'+ @prefix_var + REPLACE(c.name,' ','_') + ' = '' + CASE WHEN @v_BilletId IS NULL THEN ''NULL'' ELSE '''''''' + CAST(@'+ @prefix_var + REPLACE(c.name,' ','_') + ' AS NVARCHAR(MAX)) + '''''''' END;'
6 as [-- print value]
7FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
8WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
9ORDER BY column_id

Create table

 1
 2--- Create table
 3SET NOCOUNT ON;
 4SELECT distinct
 5    CASE c.column_id WHEN 1 THEN '  ' ELSE ', ' END
 6    + '['+ @prefix_col + c.name + '] '
 7    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END
 8    + isnull(CASE WHEN c.is_nullable = 0 THEN ' NOT ' ELSE NULL END, ' ') + 'NULL'
 9    AS [-- Columns to create table]
10FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
11WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Declare variables

 1
 2-- Declare variables
 3SET NOCOUNT ON;
 4SELECT distinct
 5    'DECLARE '
 6    + '@'+ @prefix_var + REPLACE(c.name,' ','_')
 7    + ' AS '
 8    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END
 9    AS [-- Declare variables]
10FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
11WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))

Tranform selected data to XML and XML to Table

 1
 2-- Tranform selected data to XML and XML to Table
 3SET NOCOUNT ON;
 4declare @t table (n int identity, line nvarchar(max));
 5declare @xml xml
 6SELECT @xml = ( SELECT * FROM #temp as [RowData] FOR XML AUTO, elements, root('TableData') )
 7INSERT INTO @t VALUES ('DECLARE @xml AS XML;');
 8INSERT INTO @t VALUES ('SET @xml = '''+REPLACE ( CAST( @xml as NVARCHAR(max)) , '''', '''''''')+ ''';')
 9INSERT INTO @t
10SELECT
11    CASE c.column_id WHEN 1 THEN 'SELECT  ' ELSE ', ' END
12    + 'Tbl.Col.value('''
13    + c.name
14    + '[1],'', '''
15    + t.name + CASE WHEN t.name in ('nvarchar', 'nchar', 'varchar', 'char', 'binary') THEN CASE WHEN c.max_length in (-1,2147483647) THEN '(max)' ELSE ISNULL('(' + CAST(c.max_length AS VARCHAR(30)) + ')', '') END ELSE '' END + CASE WHEN t.name in ('decimal', 'NUMERIC') THEN ISNULL('(' + CAST(c.precision AS VARCHAR(30)) + ',' + CAST(c.scale AS VARCHAR(30)) + ')', '') ELSE '' END
16    + ''' ) AS ['+ @prefix_col + c.name+']'
17FROM   tempdb.sys.columns as c INNER JOIN tempdb.sys.types as t ON c.system_type_id = t.system_type_id
18WHERE  (c.object_id = OBJECT_ID('tempdb.dbo.#temp'))
19ORDER by c.column_id;
20INSERT INTO @t VALUES ('FROM   @xml.nodes(''//TableData//RowData'') Tbl(Col);');
21select line as [-- Convert XML to Table] from @t order by n