Функција која ће да направи скрипт за копирање табеле на MSSQL

ScriptTable_TF је табеларна функција (table-valued function) за Microsoft SQL Server која прави T-SQL скрипт за поновно креирање постојеће табеле у другој бази података. Уместо да се табела скриптује ручно или преко SQL Server Management Studio-а, функција се позове са називом табеле и називом циљне базе и врати скрипт као редове који могу да се прочитају, копирају и изврше.

Шта функција ради

Функција чита структуру табеле преко sys.dm_exec_describe_first_result_set и повезује је са системским погледима sys.indexes, sys.index_columns, sys.computed_columns и sys.default_constraints. За сваку колону изворне табеле враћа по један ред са три колоне:

• column_ordinal – редослед наредбе у скрипту,
• sql – T-SQL наредба,
• cname – назив колоне.

Прва колона се прави наредбом CREATE TABLE, а свака следећа се додаје наредбом ALTER TABLE ... ADD. За сваку колону скрипт задржава:

• тип податка,
• collation, када га колона има,
• NULL или NOT NULL,
• IDENTITY(1,1) за identity колоне,
• дефиницију израчунатих (computed) колона,
• примарни кључ, као NONCLUSTERED ограничење са називом PK_табела_колона,
• подразумевану вредност, као ограничење са називом DF_табела_колона.

Први и последњи ред резултата обухватају цео скрипт трансакцијом са SET XACT_ABORT ON и блоком TRY...CATCH. Ако било која наредба не успе, прављење табеле се у потпуности поништава.

Параметри

• @TABLE_NAME – назив изворне табеле у шеми dbo текуће базе,
• @ScriptForDb – назив базе за коју се прави скрипт.

Називи ограничења се пишу као идентификатори под двоструким наводницима, па могу да садрже све знакове које садржи назив табеле или колоне. Зато скрипт мора да се изврши уз SET QUOTED_IDENTIFIER ON, што је подразумевано подешавање у SQL Server Management Studio-у.

Код функције

Код почиње са ALTER FUNCTION; при првој инсталацији то треба заменити са CREATE FUNCTION.

ALTER FUNCTION [dbo].[ScriptTable_TF](
    @TABLE_NAME nvarchar(127),
	@ScriptForDb nvarchar(127))
returns @t table(column_ordinal int, sql nvarchar(4000), cname nvarchar(127))
as
begin

insert into @t(column_ordinal, sql, cname) values(0, 'SET XACT_ABORT ON;  BEGIN TRAN  BEGIN TRY', '')

;WITH A AS
(
SELECT  
        c.is_identity_column
	  , c.column_ordinal
	  , c.name
      , c.is_nullable
	  , c.system_type_name
	  , c.collation_name
	  , c.is_xml_document
	  , c.is_part_of_unique_key
	  , c.is_computed_column
	  , cc.definition computed_definition
	  , dc.definition default_constraint
	  , (SELECT  sc.name AS ColumnName
		FROM    sys.indexes AS i INNER JOIN 
				sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
										AND i.index_id = ic.index_id JOIN
	            sys.columns sc on ic.column_id = sc.column_id and ic.object_id = sc.object_id
		WHERE   i.is_primary_key = 1
			AND OBJECT_NAME(I.object_id) = @TABLE_NAME
			AND sc.name collate Latin1_General_CI_AS  = c.name collate Latin1_General_CI_AS 
			) pk
FROM sys.dm_exec_describe_first_result_set('select * from [dbo].[' + @TABLE_NAME +']', NULL, 0) c left JOIN
    sys.computed_columns cc on OBJECT_NAME(cc.object_id) = @TABLE_NAME and cc.name collate Latin1_General_CI_AS = c.name collate Latin1_General_CI_AS left join
	sys.default_constraints dc on OBJECT_NAME(parent_object_id) = @TABLE_NAME and c.name = COL_NAME(parent_object_id, parent_column_id)
)
insert into @t(column_ordinal, sql, cname)
SELECT TOP 100 PERCENT column_ordinal, 
CASE WHEN column_ordinal = 1 THEN 
	'CREATE TABLE "' + @ScriptForDb + '".dbo."' + @TABLE_NAME + '" ("' + NAME + '"'  
ELSE 
	'ALTER TABLE "' + @ScriptForDb + '".dbo."' + @TABLE_NAME + '" ADD "' + NAME + '"'
END +
CASE WHEN is_computed_column = 1 THEN 
	' AS ' + computed_definition COLLATE Latin1_General_CI_AS
ELSE 
	' ' + system_type_name + 
	CASE WHEN collation_name IS NOT NULL THEN 
		' COLLATE ' + collation_name 
	ELSE
		''
	END +
	CASE WHEN is_nullable = 0 THEN 
		' NOT' 
	ELSE
		''
	END + 
	' NULL'+
	CASE WHEN is_identity_column = 1 THEN
		' IDENTITY(1,1)'
	ELSE
		''
	END 
END +
CASE WHEN column_ordinal = 1 THEN 
		')' 
ELSE 
		''
END +
CASE WHEN pk IS NOT NULL THEN 
		';ALTER TABLE "' + @ScriptForDb + '"."dbo"."' + @TABLE_NAME + '" ADD CONSTRAINT "PK_' + @TABLE_NAME + '_' + NAME + '" PRIMARY KEY NONCLUSTERED ("' + NAME + '") '
     WHEN default_constraint IS NOT NULL THEN 
		';ALTER TABLE "' + @ScriptForDb + '"."dbo"."' + @TABLE_NAME + '" ADD CONSTRAINT "DF_' + @TABLE_NAME + '_' + NAME + '" DEFAULT ' + default_constraint + ' FOR "' + NAME + '"'
	ELSE
		''
END
SQL, NAME
FROM A
ORDER BY column_ordinal

insert into @t(column_ordinal, sql, cname) values((select count(*) from @t) + 1, 'END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRAN; THROW; END END CATCH; IF (XACT_STATE()) = 1  COMMIT TRAN;', '')

return 
end
  

Пример употребе

Редове треба читати по редоследу колоне column_ordinal:

SELECT sql
FROM dbo.ScriptTable_TF('Kupci', 'CiljnaBaza')
ORDER BY column_ordinal

Добијени редови се, тим редом, копирају у нови прозор за упите и изврше на серверу на ком је циљна база. Пошто је назив табеле у скрипту наведен заједно са називом базе, скрипт може да се покрене из било које базе на истом серверу.

Ограничења

• подржане су само табеле у шеми dbo,
• страни кључеви, индекси осим примарног кључа, CHECK ограничења и тригери се не скриптују,
• примарни кључ се увек прави као NONCLUSTERED и по једној колони, па табела са сложеним примарним кључем тражи ручну дораду,
• IDENTITY увек добија почетну вредност и корак 1,1, без обзира на оригиналне вредности,
• скриптује се само структура, не и подаци.