Funkcija koja će da napravi skript za kopiranje tabele na MSSQL

ScriptTable_TF je tabelarna funkcija (table-valued function) za Microsoft SQL Server koja pravi T-SQL skript za ponovno kreiranje postojeće tabele u drugoj bazi podataka. Umesto da se tabela skriptuje ručno ili preko SQL Server Management Studija, funkcija se pozove sa nazivom tabele i nazivom ciljne baze i vrati skript kao redove koji mogu da se pročitaju, kopiraju i izvrše.

Šta funkcija radi

Funkcija čita strukturu tabele preko sys.dm_exec_describe_first_result_set i povezuje je sa sistemskim pogledima sys.indexes, sys.index_columns, sys.computed_columns i sys.default_constraints. Za svaku kolonu izvorne tabele vraća po jedan red sa tri kolone:

• column_ordinal – redosled naredbe u skriptu,
• sql – T-SQL naredba,
• cname – naziv kolone.

Prva kolona se pravi naredbom CREATE TABLE, a svaka sledeća se dodaje naredbom ALTER TABLE ... ADD. Za svaku kolonu skript zadržava:

• tip podatka,
• collation, kada ga kolona ima,
• NULL ili NOT NULL,
• IDENTITY(1,1) za identity kolone,
• definiciju izračunatih (computed) kolona,
• primarni ključ, kao NONCLUSTERED ograničenje sa nazivom PK_tabela_kolona,
• podrazumevanu vrednost, kao ograničenje sa nazivom DF_tabela_kolona.

Prvi i poslednji red rezultata obuhvataju ceo skript transakcijom sa SET XACT_ABORT ON i blokom TRY...CATCH. Ako bilo koja naredba ne uspe, pravljenje tabele se u potpunosti poništava.

Parametri

• @TABLE_NAME – naziv izvorne tabele u šemi dbo tekuće baze,
• @ScriptForDb – naziv baze za koju se pravi skript.

Nazivi ograničenja se pišu kao identifikatori pod dvostrukim navodnicima, pa mogu da sadrže sve znakove koje sadrži naziv tabele ili kolone. Zato skript mora da se izvrši uz SET QUOTED_IDENTIFIER ON, što je podrazumevano podešavanje u SQL Server Management Studiju.

Kod funkcije

Kod počinje sa ALTER FUNCTION; pri prvoj instalaciji to treba zameniti sa 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
  

Primer upotrebe

Redove treba čitati po redosledu kolone column_ordinal:

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

Dobijeni redovi se, tim redom, kopiraju u novi prozor za upite i izvrše na serveru na kom je ciljna baza. Pošto je naziv tabele u skriptu naveden zajedno sa nazivom baze, skript može da se pokrene iz bilo koje baze na istom serveru.

Ograničenja

• podržane su samo tabele u šemi dbo,
• strani ključevi, indeksi osim primarnog ključa, CHECK ograničenja i trigeri se ne skriptuju,
• primarni ključ se uvek pravi kao NONCLUSTERED i po jednoj koloni, pa tabela sa složenim primarnim ključem traži ručnu doradu,
• IDENTITY uvek dobija početnu vrednost i korak 1,1, bez obzira na originalne vrednosti,
• skriptuje se samo struktura, ne i podaci.