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

    Ova funkcija će da napravi skript za kopiranje tabele na MSSQL.

    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
      

    Ako tražite usluge razvoja softvera najvišeg ranga, ne tražite dalje!

    Šta nudim:

    • Razvoj veb stranica: Pretvorite vašu ideju u potpuno funkcionalnu veb stranicu.
    • Kreacija mobilnih aplikacija: Dostignite vašu publiku na svakom uređaju.
    • Prilagođena softverska rešenja: Softver prilagođen vašim poslovnim potrebama.
    • Upravljanje bazom podataka: Osigurajte da su vaši podaci strukturirani, sigurni i dostupni.
    • Konsultacije: Niste sigurni gde da počnete? Razgovarajmo o najboljim tehnološkim rešenjima za vaše ciljeve.

    Sa godinama iskustva u tehničkoj industriji, usavršio sam svoje veštine kako bih pružio samo najbolje svojim klijentima. Pretvorimo vašu viziju u stvarnost. Kontaktirajte me danas da započnemo vaš sledeći digitalni projekat!