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!
Ostavite odgovor