ScriptTable_TF

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

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_' + DBO.StripOut_FN(@TABLE_NAME, '%[^A-Za-z0-9_]%') + '_' + DBO.StripOut_FN(NAME, '%[^A-Za-z0-9_]%') + ' PRIMARY KEY NONCLUSTERED ("' + NAME + '") '
     WHEN default_constraint IS NOT NULL THEN 
		';ALTER TABLE "' + @ScriptForDb + '"."dbo"."' + @TABLE_NAME + '" ADD CONSTRAINT DF_' + DBO.StripOut_FN(@TABLE_NAME, '%[^A-Za-z0-9_]%') + '_' + DBO.StripOut_FN(NAME, '%[^A-Za-z0-9_]%') + ' 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