Dodawanie tożsamości do istniejącej kolumny

W SQL 2005 i nowszych, istnieje sztuczka, aby rozwiązać ten problem bez zmiany stron danych tabeli. Jest to ważne w przypadku dużych tabel, w których dotykanie każdej strony danych może zająć kilka minut lub godzin. Sztuczka działa również wtedy, gdy kolumna tożsamości jest kluczem podstawowym, jest częścią klastrowego lub nieklastrycznego indeksu lub innych gotchów, które mogą potknąć się prostszym rozwiązaniem "Dodaj/Usuń / Zmień nazwę kolumny".

Oto sztuczka: możesz użyć tabeli ALTER SQL Server...SWITCH Instrukcja zmiany schematu tabeli bez zmiany danych, co oznacza, że można zastąpić tabelę tożsamością identycznym schematem tabeli, ale bez kolumny tożsamości. Ta sama sztuczka działa, aby dodać tożsamość do istniejącej kolumny.

Normalnie, Zmień tabelę...SWITCH służy do wydajnego zastąpienia pełnej partycji w podzielonej tabeli nową, pustą partycją. Ale może być również używany w tabelach bez partycji.

Użyłem tej sztuczki, aby przekonwertować, w mniej niż 5 sekund, kolumna z tabeli 2,5 miliarda wierszy od tożsamości do nie-tożsamości (w celu uruchomienia wielogodzinnego zapytania, którego plan kwerend działał lepiej dla kolumn nie-tożsamości), a następnie przywrócił ustawienie tożsamości, ponownie w mniej niż 5 sekund.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

Jest to oczywiście bardziej zaangażowane niż rozwiązania w innych odpowiedziach, ale jeśli twój stół jest duży, może to być prawdziwe oszczędności życia. Są pewne zastrzeżenia:

• jako o ile wiem, tożsamość jest jedyną rzeczą, którą można zmienić w kolumnach tabeli za pomocą tej metody. Dodawanie / usuwanie kolumn, zmiana nullability itp. nie wolno.
• musisz upuścić klucze foriegn przed wykonaniem przełącznika i przywrócić je po.
• to samo dotyczy funkcji SCHEMABINDING, widoków itp.
• indeksy nowej tabeli muszą być dokładnie dopasowane (te same kolumny, ta sama kolejność itp.)
• stare i nowe tabele muszą znajdować się na tej samej grupie plików.
• tylko działa na SQL Server 2005 lub nowszym
• wcześniej uważałem, że ta sztuczka działa tylko na wersjach Enterprise lub Developer SQL Server (ponieważ partycje są obsługiwane tylko w wersjach Enterprise i Developer), ale Mason G. Zhwiti w swoim komentarzu poniżej mówi, że działa również w SQL Standard Edition zbyt. Zakładam, że oznacza to, że ograniczenie do Enterprise lub Developer nie ma zastosowania do ALTER TABLE...Zamiana.

Jest dobry artykuł o Technecie wyszczególnienie powyższych wymagań.

UPDATE - Eric Wu miał komentarz poniżej, który dodaje ważne informacje o tym rozwiązaniu. Kopiowanie go tutaj, aby upewnić się, że przyciągnie więcej uwagi:

Jest jeszcze jedno zastrzeżenie, o którym warto wspomnieć. / Align = "left" / nowa tabela chętnie otrzyma dane ze starej tabeli, a wszystkie nowe wiersze zostaną wstawione zgodnie ze wzorem tożsamości, będą zacznij od 1 i potencjalnie złamać, jeśli wspomniana kolumna jest kluczem podstawowym. Zastanów się nad uruchomieniem DBCC CHECKIDENT('<newTableName>') natychmiast po przełączam się. Zobacz msdn.microsoft.com/en-us/library/ms176057.aspx więcej info.

Jeśli tabela jest aktywnie rozszerzana o nowe wiersze (co oznacza, że nie ma zbyt wiele przestojów między dodaniem tożsamości a dodaniem nowych wierszy, to zamiast DBCC CHECKIDENT będziesz chciał ręcznie ustawić wartość początkową tożsamości w nowym schemacie tabeli, aby była większa niż największy istniejący identyfikator w tabeli, np. IDENTITY (2435457, 1). Możesz być w stanie uwzględnić zarówno ALTER TABLE...SWITCH i DBCC CHECKIDENT w transakcji (lub nie -- nie testowałem tego), ale wydaje się, że ręczne ustawienie wartości zalążkowej będzie łatwiejsze i bezpieczniejsze.

Oczywiście, jeśli do tabeli nie są dodawane nowe wiersze (lub są dodawane tylko sporadycznie, jak w codziennym procesie ETL), to ten warunek wyścigu nie nastąpi, więc DBCC CHECKIDENT jest w porządku.

Nie można zmienić kolumny na kolumnę tożsamości. Musisz utworzyć nową kolumnę, która jest zdefiniowana jako tożsamość od początku, a następnie upuścić starą kolumnę i zmienić nazwę Nowej na starą nazwę.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

Jest fajne rozwiązanie opisane tutaj: SQL SERVER-Dodaj lub Usuń właściwość Identity na kolumnie

W skrócie edytuj ręcznie tabelę w SQL managerze, Zmień tożsamość, nie zapisuj zmian, po prostu pokaż skrypt, który zostanie utworzony dla zmian, skopiuj go i użyj później.

Jest to ogromna oszczędność czasu, ponieważ (skrypt) zawiera wszystkie klucze obce, indeksy itp. związane ze stołem, który zmieniasz. Piszę to ręcznie... Broń Boże.

Proste wyjaśnienie

Zmień nazwę istniejącej kolumny używając sp_RENAME

EXEC sp_RENAME ' Table_Name.Existing_ColumnName', 'New_ColumnName','COLUMN'

Przykład zmiany nazwy:

Istniejąca kolumna UserID zmienia nazwę na OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Następnie dodaj nową kolumnę za pomocą zapytania alter, aby ustawić jako klucz podstawowy i wartość tożsamości

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Przykład dla ustawionego klucza podstawowego

Nowo utworzona nazwa kolumny to UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Następnie upuść zmienioną kolumnę

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Przykład dla kolumny Drop

ALTER TABLE Users DROP COLUMN OldUserID

Teraz dodaliśmy primarykey i identity do istniejącej kolumny w tabeli.

W sql server 2014 (Nie wiem jak niższe wersje) możesz to zrobić po prostu, używając sekwencji.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

Stąd: Sekwencja jako domyślna wartość kolumny

Jestem programistą Javy, który znalazł się w zespole bez DBA i takim, w którym jako programista nie mogę uzyskać praw do DBA. Miałem za zadanie przenieść cały schemat między dwoma bazami danych, więc bez dba musiałem to zrobić i zrobić to uruchamiając skrypty, nie będąc w stanie korzystać z GUI W Sql Server 2008, ponieważ nie miałem uprawnień administratora.

Wszystko zostało przeniesione bez problemu, jednak podczas uruchamiania procedury składowanej na nowym schemacie.table, I found I lost the identity pole w tabeli. Dwukrotnie sprawdziłem skrypt, który stworzył tabelę i tam był, jednak SQL Server nie dostał go, gdy uruchomiłem skrypt. Powiedziano mi później przez DBA, że widział ten sam problem wcześniej.

W każdym razie, dla SQL Server 2008, są to kroki, które podjąłem, aby to rozwiązać i zadziałały, więc zamieszczam to tutaj w nadziei, że będzie to pomoc dla kogoś. To właśnie zrobiłem, ponieważ miałem FK zależności na innym stole, który sprawił, że to bardziej trudne:

Użyłem tego zapytania, aby zweryfikować brak tożsamości i wyświetlić zależności w tabeli.

1.) Znajdź statystyki w tabeli:

exec sp_help 'dbo.table_name_old';

2.) Utwórz zduplikowaną, identyczną nową tabelę, z wyjątkiem dodania pola tożsamości w polu PK, w którym była wcześniej.

3.) Wyłącz tożsamość, aby przenieść dane.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Przenieść dane.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Sprawdź, czy dane tam są.

SELECT * FROM dbo.table_name_new

6.) Ponownie włączyć tożsamość.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) jest to najlepszy skrypt, jaki znalazłem, aby uzyskać wszystkie relacje FK, aby sprawdzić, które tabele odnoszą się do oryginalnej tabeli jako zależności i natknąłem się na wiele, więc jest to opiekun!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Upewnij się, że masz wszystkie skrypty PK i FK dla wszystkich tabel zaangażowanych, przed następnym krokiem.

9.) Możesz kliknąć prawym przyciskiem myszy na każdym kluczu i skrypt ten za pomocą SQL Server 2008

10.) Upuść FK(y) z tabeli(y) zależności za pomocą ta składnia:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Upuść oryginalną tabelę:

DROP TABLE dbo.table_name_old;

13.) Te kolejne kroki opierają się na skryptach utworzonych w SQL Server 2008 w kroku 9.

-- Dodaj PK do nowej tabeli.

-- Dodaj FK do nowej tabeli.

-- Dodaj z powrotem FK do tabeli zależności.

14.) Sprawdzić, czy wszystko jest poprawne i kompletne. Użyłem interfejsu graficznego do przeglądania stołów.

15.) Zmień nazwę nowej tabeli na oryginalną nazwisko.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Z założenia nie ma prostego sposobu na włączenie lub wyłączenie funkcji tożsamości dla istniejącej kolumny. Jedynym czystym sposobem na to jest utworzenie nowej kolumny i przekształcenie jej w kolumnę tożsamości lub utworzenie nowej tabeli i migracja danych.

Jeśli użyjemy SQL Server Management Studio, aby pozbyć się wartości identity w kolumnie "id", zostanie utworzona nowa tabela tymczasowa, dane zostaną przeniesione do tabeli tymczasowej, stara tabela zostanie usunięta i nowa tabela zostanie zmieniona.

Użyj Zarządzania Studio, aby dokonać zmiany, a następnie kliknij prawym przyciskiem myszy w Projektancie i wybierz "Generuj skrypt zmiany".

Zobaczysz, że to właśnie robi SQL server w tle.

Nie możesz tego zrobić w ten sposób, musisz dodać kolejną kolumnę, upuścić oryginalną kolumnę i zmienić nazwę nowej kolumny lub utworzyć nową tabelę, skopiować dane i upuścić starą tabelę, a następnie zmienić nazwę nowej tabeli na starą tabelę

Jeśli używasz SSMS i ustawiasz właściwość identity na ON w Projektancie oto, co SQL Server robi za kulisami. Więc jeśli masz tabelę o nazwie [user], to dzieje się tak, jeśli utworzysz UserID i identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Powiedziawszy, że istnieje sposób zhakować tabelę systemową, aby to osiągnąć, ustawiając wartość bitową, ale to nie jest obsługiwane i nie zrobiłbym tego

Przyjęta odpowiedź jest błędna: możesz zmienić istniejącą kolumnę na tożsamość tak długo, jak {[2] } nie zawiera żadnych wartości null. Po zmianie Seed tożsamości rozpocznie się od max (kolumna) + 1.

Więc to, co naprawdę musisz zrobić, to podać wartości dla wszystkich null.

Jak rozumiem w normalnych przypadkach tworzymy tabelę z kluczem głównym który ma właściwość tożsamości
Więc Zmień nazwę lub Usuń kolumnę, która jest powiązana z kluczem głównym constraint nie będzie możliwe, ponieważ reguły constraint sprawdzają poprawność struktury kolumn.
Aby to osiągnąć, musimy wykonać kilka kroków w następujący sposób:
Załóżmy, że TableName = 'pracownik' i ColumnName = "EmployeeId"

1. Dodaj nową kolumnę 'EmployeeId_new' w tabeli 'Employee'
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

1. Teraz Usuń kolumnę 'EmployeeId' z tabeli 'Employee'
   ALTER TABLE Employee Drop COLUMN EmployeeId

2. Spowoduje to błąd ze względu na obowiązujące reguły ograniczenia klucza podstawowego i walidację struktury kolumn.
   *### 'Msg 5074, Poziom 16, Stan 1, Linia 1 obiekt [PK_dbo.Pracownik] jest zależny od colmn [EmployeeId].'###

3. Musimy więc najpierw usunąć podstawowe ograniczenie klucza z tabeli "pracownik" , a następnie możemy usunąć kolumnę
   ALTER TABLE Employee DROP constraint [PK_dbo.Pracownik]

4. Teraz możemy usunąć kolumnę "EmployeeId" z tabeli "Employee", tak jak w poprzednim kroku, w którym pojawił się błąd
   ALTER TABLE Employee Drop COLUMN EmployeeId

5. Teraz Kolumna 'EmployeeId' usunięta z tabeli Tak więc zmienimy nazwę nowo dodanej nowej kolumny 'EmployeeId_new' na 'EmployeeId'
   pracownik sp_rename.EmployeeId", "EmployeeId_new", "kolumna'

6. Aby zmienić tabelę w takiej samej formie jak była, musimy dodać podstawowe ograniczenie klucza dla kolumny 'EmployeeId'
   ALTER TABLE Employee add constraint [PK_dbo.Pracownik] klucz podstawowy (EmployeeId)

8. teraz tabela 'EmployeeID' z 'EmployeeId' jest modyfikowana dla reguł tożsamości wraz z istniejącym ograniczeniem klucza głównego

Nie ma, niestety; właściwość IDENTITY należy do tabeli, a nie kolumny.

Łatwiej jest to zrobić w GUI, ale jeśli nie jest to opcja, możesz przejść długą drogę kopiowania danych, upuszczania kolumny, ponownego dodawania jej z tożsamością i umieszczania danych z powrotem.

Zobacz tutaj na konto cios po cios.

Kliknij prawym przyciskiem myszy nazwę tabeli w Eksploratorze obiektów. Dostaniesz kilka opcji. Kliknij na 'Design'. Nowa karta zostanie otwarta dla tej tabeli. Możesz dodać ograniczenie tożsamości tutaj w 'Właściwości kolumny'.

Aby zmodyfikować właściwości tożsamości dla kolumny:

• w Eksploratorze Server kliknij prawym przyciskiem myszy tabelę z właściwościami tożsamości, którą chcesz zmodyfikować, i kliknij Otwórz definicję tabeli. Tabela zostanie otwarta w tabeli projektant.
• wyczyść pole wyboru Zezwól na NULL dla kolumny, którą chcesz zmienić.
• na karcie Właściwości kolumny rozwiń właściwość specyfikacji tożsamości.
• kliknij komórkę siatki dla właściwości potomnej is Identity i wybierz Tak z listy rozwijanej lista.
• wpisz wartość w komórce nasiennej tożsamości. Wartość ta zostanie przypisana do pierwszego wiersza tabeli. Wartość 1 zostanie przypisana domyślnie.

That ' s it, and it worked for me

Jeśli oryginalny plakat rzeczywiście chciał ustawić istniejącą kolumnę jako PRIMARY KEY dla tabeli i faktycznie nie potrzebował kolumny jako IDENTITY (dwie różne rzeczy), to można to zrobić za pomocą t-SQL za pomocą:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Zwróć uwagę na nawias wokół nazwy kolumny po opcji PRIMARY KEY.

Chociaż ten post jest stary i zakładam, że potrzebujesz requesterów, uznałem, że te dodatkowe informacje mogą być pomocne dla użytkowników napotykających ten wątek, ponieważ wierz, że rozmowa może doprowadzić do przekonania, że istniejąca kolumna nie może być ustawiona jako klucz podstawowy bez dodania go jako nowej kolumny, co byłoby nieprawidłowe.

Zgodnie z moim obecnym stanem, podążam za tym podejściem. Chcę nadać tożsamość podstawowej tabeli po danych wstawionych przez skrypt.

Ponieważ chcę dołączyć tożsamość, więc zawsze zaczyna się od 1 do końca liczby rekordów, które chcę.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

Spowoduje to utworzenie tej samej kolumny klucza głównego z identyfikatorem

Użyłem tego linku : https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Dodaj klucz podstawowy do istniejącej tabeli

Nie wierzę, że można zmienić istniejącą kolumnę na kolumnę tożsamości za pomocą tsql. Można to jednak zrobić za pomocą widoku Projekt Menedżera przedsiębiorstwa.

Alternatywnie możesz utworzyć nowy wiersz jako kolumnę tożsamości, upuścić starą kolumnę, a następnie zmienić nazwę nowej kolumny.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

Zasadniczo istnieją cztery logiczne kroki.

1. Utwórz nową kolumnę tożsamości. Włącz Wstaw tożsamość dla tej nowej kolumny.

2. Wstaw dane z kolumny źródłowej (kolumny, którą chcesz przekonwertować na tożsamość) do nowej kolumny.

3. Wyłącz identyfikator Wstaw dla nowej kolumny.

4. Upuść kolumnę źródłową i zmień nazwę nowej kolumny na nazwę kolumny źródłowej.

Może być więcej kompleksów jak praca na wielu serwerach itp.

Proszę zapoznać się z poniższym artykułem dla kroków (używając ssms & T-sql). Kroki te są przeznaczone dla początkujących z mniejszą przyczepnością do T-SQL.

Http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

Generuje skrypt dla wszystkich tabel z kluczem głównym = bigint, które nie mają zestawu tożsamości; zwróci To listę wygenerowanych skryptów dla każdej tabeli;

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 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
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
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
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;