TableScriptGenerator

A procedure [gen].[table_script] with subroutines to generate scripts of table variable declaration upon tables, views, table functions and some select statements for the current database. Data fill may or may not be included depending on @df_type parameter value (included by default).

This procedure is a tool that helps to write or rewrite sql statements, gerenate data filling scripts.

EXEC [gen].[table_script] @object_name_or_query = '<OBJECT NAME>'

Examples

Suggest there is a table:

create table [Test]
(
  [Id]    int not null primary key clustered,
  [Value] nvarchar(500) null
)

insert [Test] values (1, 'One'), (2, 'Two'), (3, 'Three')

#1 execution

exec [gen].[table_script] 'select * from Test where Id < 3'

output

-- Скрипт таблицы [gen].[table_script_output]
declare @table_script_output table table
(
  [Id]    int           not null,
  [Value] nvarchar(500)     null
)
insert @table_script_output
  ([Id], [Value])
values
--(Id, Value   ),
  (1,  N'One'  ),
  (2,  N'Two'  )

#2 execution

exec [gen].[table_script] 'Test'

output

-- Скрипт таблицы [Test]
declare @Test table
(
  [Id]    int           not null primary key clustered,
  [Value] nvarchar(500)     null
)
merge @Test d
using
(
  values
--(Id, Value   ),
  (1,  N'One'  ),
  (2,  N'Two'  ),
  (3,  N'Three')
) s ([Id], [Value])
on s.[Id] = d.[Id]
when not matched by target then
  insert ([Id], [Value])
  values ([Id], [Value])
when matched then
  update set
    d.[Value] = s.[Value];

Text to table helper

If you copy-paste data from MS Excel table or MS SQL Server Management Studio table or any other table having text in your clipboard where rows are separated by new lines \n and columns are separated by tabs \t, then you may want to use this data in an sql script. Simply pass this copied data as parameter to [gen].[text_to_table] procedure:

EXEC [gen].[text_to_table]
'68	BO	BOL	BOLIVIA
70	BA	BIH	BOSNIA AND HERZEGOVINA
72	BW	BWA	BOTSWANA
74	BV	BVT	BOUVET ISLAND'

output

(68, 'BO', 'BOL', 'BOLIVIA'),
(70, 'BA', 'BIH', 'BOSNIA AND HERZEGOVINA'),
(72, 'BW', 'BWA', 'BOTSWANA'),
(74, 'BV', 'BVT', 'BOUVET ISLAND')

This output then can be easily copied into a script, you will only have to add two more lines before and one line after like this:

select *
from (values
(68, 'BO', 'BOL', 'BOLIVIA'),
(70, 'BA', 'BIH', 'BOSNIA AND HERZEGOVINA'),
(72, 'BW', 'BWA', 'BOTSWANA'),
(74, 'BV', 'BVT', 'BOUVET ISLAND')
) v(id, code2, code3, name);

which returns:

id	code2	code3	name
68	BO	BOL	BOLIVIA
70	BA	BIH	BOSNIA AND HERZEGOVINA
72	BW	BWA	BOTSWANA
74	BV	BVT	BOUVET ISLAND

Installation

You may use "single script\table script generator.sql" script file for fast deployment.