Muszę zmienić klucz podstawowy tabeli na kolumnę tożsamości, a tabela zawiera już wiele wierszy.
Mam skrypt do czyszczenia identyfikatorów, aby upewnić się, że są one sekwencyjne od 1, działa dobrze w mojej testowej bazie danych.
Jaka jest komenda SQL, aby zmienić kolumnę na właściwość tożsamości?
Odpowiedzi:
Nie można zmienić istniejących kolumn pod kątem tożsamości.
Masz 2 opcje,
Utwórz nowy stół z tożsamością i upuść istniejący stół
Utwórz nową kolumnę z tożsamością i upuść istniejącą kolumnę
Podejście 1. ( Nowa tabela ) Tutaj możesz zachować istniejące wartości danych w nowo utworzonej kolumnie tożsamości.
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'Sposób 2 ( nowa kolumna ) Nie można zachować istniejące wartości danych na kolumny tożsamości nowo utworzonej, Kolumna tożsamości odbędzie sekwencję numeru.
Alter Table Names
Add Id_new Int Identity(1, 1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID', 'Column'Aby uzyskać więcej informacji, zobacz następujący post na forum Microsoft SQL Server:
IDENTITY(1, 1)część z kolumną klucza podstawowego
W SQL 2005 i nowszych istnieje sposób na rozwiązanie tego problemu bez zmiany stron danych tabeli. Jest to ważne w przypadku dużych tabel, w których dotknięcie każdej strony danych może zająć minuty lub godziny. Sztuczka działa także, nawet jeśli kolumna tożsamości jest kluczem podstawowym, jest częścią indeksu klastrowanego lub nieklastrowanego lub innych gotchas, które mogą wyzwalać prostsze rozwiązanie „dodaj / usuń / zmień nazwę kolumny”.
Oto sztuczka: możesz użyć instrukcji ALTER TABLE ... SQL SWITCH programu SQL Server, aby zmienić schemat tabeli bez zmiany danych, co oznacza, że możesz zastąpić tabelę TOŻSAMOŚCIĄ o identycznym schemacie tabeli, ale bez kolumny TOŻSAMOŚCI. Ta sama sztuczka działa, aby dodać TOŻSAMOŚĆ do istniejącej kolumny.
Zwykle ALTER TABLE ... SWITCH służy do skutecznego zastąpienia pełnej partycji w podzielonej na partycje tabeli nową, pustą partycją. Ale może być również używany w niepodzielonych na partycje tabelach.
Użyłem tej sztuczki, aby w ciągu 5 sekund przekonwertować kolumnę tabeli o wartości 2,5 miliarda wierszy z IDENTITY na non-TOŻSAMOŚĆ (w celu uruchomienia wielogodzinnego zapytania, którego plan zapytań działał lepiej w przypadku braku tożsamości kolumny), a następnie przywrócono ustawienie TOŻSAMOŚĆ, ponownie w mniej niż 5 sekund.
Oto przykładowy kod tego, jak to działa.
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ć naprawdę uratowanie życia. Istnieje kilka zastrzeżeń:
Jest dobry artykuł na temat TechNet szczegółowo opisujący powyższe wymagania.
AKTUALIZACJA - Eric Wu miał poniżej komentarz, który dodaje ważne informacje o tym rozwiązaniu. Skopiuj go tutaj, aby mieć większą uwagę:
Jest jeszcze jedno zastrzeżenie, o którym warto wspomnieć. Chociaż nowa tabela z przyjemnością odbierze dane ze starej tabeli, a wszystkie nowe wiersze zostaną wstawione zgodnie ze wzorem tożsamości, zaczną od 1 i potencjalnie pękną, jeśli wspomniana kolumna jest kluczem podstawowym. Rozważ uruchomienie
DBCC CHECKIDENT('<newTableName>')natychmiast po zmianie. Aby uzyskać więcej informacji, zobacz msdn.microsoft.com/en-us/library/ms176057.aspx .
Jeśli tabela jest aktywnie rozszerzana o nowe wiersze (co oznacza, że nie masz dużo, jeśli masz jakiekolwiek przestoje między dodaniem TOŻSAMOŚCI a dodaniem nowych wierszy, to zamiast tego DBCC CHECKIDENTbędziesz chciał ręcznie ustawić wartość początkową tożsamości w nowym schemacie tabeli na większy niż największy istniejącego ID w tabeli, np IDENTITY (2435457, 1). może być w stanie uwzględnić zarówno ALTER TABLE...SWITCHa DBCC CHECKIDENTw transakcji (lub not-- nie testowałem tego), ale wydaje się, że ustawienie wartości początkowej ręcznie będzie łatwiejsze i bezpieczniejsze.
Oczywiście, jeśli żadne nowe wiersze nie są dodawane do tabeli (lub są dodawane tylko sporadycznie, jak codzienny proces ETL), to ten warunek wyścigu nie nastąpi, więc DBCC CHECKIDENTjest w porządku.
DBCC CHECKIDENT('<newTableName>')natychmiast po zmianie. Aby uzyskać więcej informacji, zobacz msdn.microsoft.com/en-us/library/ms176057.aspx .
Nie możesz zmienić kolumny na kolumnę TOŻSAMOŚCI. To, co musisz zrobić, to utworzyć nową kolumnę, która jest zdefiniowana jako TOŻSAMOŚĆ od samego początku, a następnie upuść starą kolumnę i zmień nazwę nowej na starą.
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: SERWER SQL - Dodaj lub usuń właściwość tożsamości w kolumnie
W skrócie ręcznie edytuj swoją tabelę w SQL Managerze, przełącz tożsamość, NIE ZAPISUJ zmian, po prostu pokaż skrypt, który zostanie utworzony dla zmian, skopiuj go i użyj go później.
Jest to ogromna oszczędność czasu, ponieważ (skrypt) zawiera wszystkie klucze obce, indeksy itp. Związane ze zmienianą tabelą. Pisanie tego ręcznie ... Boże broń.
Rozważ użycie SEKWENCJI zamiast TOŻSAMOŚCI .
W SQL Server 2014 (nie wiem o niższych wersjach) 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_nameProste wyjaśnienie
Zmień nazwę istniejącej kolumny za pomocą sp_RENAME
EXEC sp_RENAME „Table_Name.Existing_ColumnName”, „New_ColumnName”, „COLUMN”
Przykład zmiany nazwy:
Nazwa istniejącej kolumny UserID zostaje zmieniona 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 ustawienia klucza podstawowego
Nowo utworzona nazwa kolumny to UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)następnie upuść nazwę kolumny o zmienionej nazwie
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnNamePrzykład dla kolumny o zmienionej nazwie
ALTER TABLE Users DROP COLUMN OldUserIDTeraz dodaliśmy klucz podstawowy i tożsamość do istniejącej kolumny w tabeli.
Jestem programistą Java, który akurat dostał się do zespołu bez DBA, a jako programista nie mogę uzyskać uprawnień DBA. Zadanie polegało na przeniesieniu całego schematu między dwiema bazami danych, więc bez DBA musiałem to zrobić, wykonując skrypty, nie mogąc 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. Schema.table odkryłem, że straciłem pole tożsamości w tabeli. Dokładnie sprawdziłem skrypt, który utworzył tabelę i tam był, jednak SQL Server nie dostał go, kiedy uruchomiłem skrypt. DBA powiedział mi później, że widział ten sam problem wcześniej.
W każdym razie, dla SQL Server 2008, są to kroki, które podjąłem, aby rozwiązać ten problem i zadziałały, więc zamieszczam to tutaj w nadziei, że będzie to komuś pomocne. To właśnie zrobiłem, ponieważ miałem zależności FK od innego stołu, co utrudniło to:
Użyłem tego zapytania, aby sprawdzić, czy rzeczywiście brakuje tożsamości i zobaczyć zależności od tabeli.
1.) Znajdź statystyki na stole:
exec sp_help 'dbo.table_name_old';2.) Utwórz duplikat, identyczną nową tabelę, z wyjątkiem tego, że dodaj pole tożsamości w polu PK, w którym było wcześniej.
3.) Wyłącz tożsamość, aby przenieść dane.
SET IDENTITY_INSERT dbo.table_name ON 4.) Prześlij 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_new6.) Ponownie włącz tożsamość.
SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF7.) To najlepszy skrypt, jaki znalazłem, aby uzyskać wszystkie relacje z FK, aby zweryfikować, która tabela (-y) w oryginalnej tabeli odwołuje się do 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.) Przed następnym krokiem upewnij się, że masz wszystkie skrypty PK i FK dla wszystkich zaangażowanych tabel.
9.) Możesz kliknąć prawym przyciskiem myszy każdy klucz i wykonać skrypt za pomocą SQL Server 2008
10.) Usuń FK z tabel zależności, używając tej składni:
ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]11.) Upuść oryginalny stół:
DROP TABLE dbo.table_name_old;13.) Następne kroki opierają się na skryptach utworzonych w SQL Server 2008 w kroku 9.
- Dodaj PK do nowej tabeli.
- Dodaj FK do nowego stołu.
- Dodaj FK z powrotem do tabeli zależności.
14.) Sprawdź, czy wszystko jest poprawne i kompletne. Użyłem GUI do przeglądania tabel.
15.) Zmień nazwę nowej tabeli na pierwotną nazwę tabeli.
exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';Wreszcie wszystko działało!
nie można tego zrobić w ten sposób, należy 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ć jej nazwę na starą stół
jeśli używasz SSMS i ustawisz właściwość tożsamości na ON w projektancie, oto, co robi SQL Server za kulisami. Jeśli więc masz tabelę o nazwie [użytkownik], dzieje się tak, jeśli podasz identyfikator użytkownika i tożsamość
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
COMMITPowiedziawszy, że istnieje sposób na zhakowanie tabeli systemowej, aby to osiągnąć, ustawiając wartość bitową, ale nie jest to obsługiwane i nie zrobiłbym tego
Jak rozumieć w normalnych przypadkach tworzymy tabelę z klucza podstawowego , który jest o właściwość Identity
So Zmień nazwę lub Usuń kolumnę, która jest związana z kluczem podstawowym ograniczeniem nie będzie możliwe, ponieważ zasady ograniczające potwierdzania strukturę kolumn.
Aby to osiągnąć, musimy wykonać kilka kroków w następujący sposób:
Załóżmy, że TableName = „Employee” i ColumnName = „EmployeeId”
1. Dodaj nową kolumnę „EmployeeId_new” w tabeli „Employee”
ALTER TABLE Pracownik ADD EmployeeId_new INT TOŻSAMOŚĆ ( 1,1)
Teraz usuń kolumnę „EmployeeId” z tabeli „Employee”
ALTER TABLE Pracownik DROP COLUMN EmployeeId
Spowoduje to wygenerowanie błędu, ponieważ obowiązują reguły ograniczeń klucza głównego i sprawdzanie poprawności struktury kolumny.
* ### ' Msg 5074, poziom 16, stan 1, wiersz 1 Obiekt [PK_dbo.Employee] jest zależny od colmn [EmployeeId]. ” ###
Musimy więc najpierw usunąć ograniczenie klucza podstawowego z tabeli „Pracownik”, a następnie możemy usunąć kolumnę
ALTER TABLE Ograniczenie DROP pracownika [PK_dbo.Employee]
Teraz możemy usunąć kolumnę „EmployeeId” z tabeli „Employee”, tak jak w poprzednim kroku, w którym wystąpił błąd
ALTER TABLE Pracownik DROP COLUMN EmployeeId
Teraz kolumna „EmployeeId” usunięta z tabeli Więc zmienimy nazwę nowo dodanej nowej kolumny „EmployeeId_new” na „EmployeeId”
sp_rename „Employee.EmployeeId”, „EmployeeId_new”, „COLUMN”
Aby zmienić układ tabeli w takiej samej formie, w jakiej była, musimy dodać Ograniczenie klucza głównego dla kolumny „EmployeeId”
ALTER TABLE Pracownik dodać ograniczenie [PK_dbo.Employee] klucz podstawowy (EmployeeId)
8. Teraz tabela „Pracownik” z „EmployeeId” została zmodyfikowana dla reguł tożsamości wraz z istniejącym ograniczeniem klucza podstawowego
Z założenia nie ma prostego sposobu włączenia lub wyłączenia funkcji tożsamości dla istniejącej kolumny. Jedynym czystym sposobem na to jest utworzenie nowej kolumny i uczynienie z niej kolumny tożsamości lub utworzenie nowej tabeli i migracja danych.
Jeśli użyjemy SQL Server Management Studio, aby pozbyć się wartości tożsamości w kolumnie „id”, tworzona jest nowa tabela tymczasowa, dane są przenoszone do tabeli tymczasowej, stara tabela jest usuwana, a nowa tabela jest zmieniana.
Użyj Management Studio, aby wprowadzić zmiany, a następnie kliknij projektanta prawym przyciskiem myszy i wybierz „Generuj skrypt zmian”.
Zobaczysz, że to właśnie robi serwer SQL w tle.
Niestety nie ma jednego; właściwość TOŻSAMOŚĆ należy raczej do tabeli niż do kolumny.
Najprostszym sposobem jest zrobienie tego w GUI, ale jeśli nie jest to opcja, możesz przejść długą drogę kopiowania danych, upuszczania kolumny, dodawania jej z tożsamością i przywracania danych.
Zobacz tutaj konto blow-by-blow.
Aby zmodyfikować właściwości tożsamości dla kolumny:
To wszystko i zadziałało dla mnie
Jeśli akurat używasz programu Visual Studio 2017+
To wszystko dla ciebie zrobi.
Jeśli oryginalny plakat naprawdę chciał ustawić istniejącą kolumnę PRIMARY KEYna tabelę i faktycznie nie potrzebował kolumny jako IDENTITYkolumny (dwie różne rzeczy), 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 PRIMARY KEYopcji.
Chociaż ten post jest stary i zakładam, że potrzeby osób żądających, uważam, że te dodatkowe informacje mogą być pomocne dla użytkowników napotykających ten wątek, ponieważ uważam, że rozmowa może prowadzić do przekonania, że istniejącej kolumny nie można ustawić jako klucz podstawowy bez dodawania go jako nowej kolumny, co byłoby niepoprawne.
Zgodnie z moim obecnym stanem stosuję to podejście. Chcę nadać tożsamość tabeli podstawowej po danych wstawionych za pomocą skryptu.
Ponieważ chcę dołączyć tożsamość, zawsze zaczynam od 1 do końca rekordu, który 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)
GOSpowoduje to utworzenie tej samej kolumny klucza podstawowego z tożsamością
Użyłem tego linku: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/
Nie sądzę, że można zmienić istniejącą kolumnę na kolumnę tożsamości za pomocą tsql. Można to jednak zrobić w widoku projektu Enterprise Manager.
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 CLUSTEREDZasadniczo istnieją cztery logiczne kroki.
Utwórz nową kolumnę Tożsamość. Włącz Wstaw tożsamość dla tej nowej kolumny.
Wstaw dane z kolumny źródłowej (kolumny, którą chcesz przekonwertować na Tożsamość) do tej nowej kolumny.
Wyłącz wstawianie tożsamości dla nowej kolumny.
Upuść kolumnę źródłową i zmień nazwę nowej kolumny na nazwę kolumny źródłowej.
Mogą występować dodatkowe trudności, takie jak praca na wielu serwerach itp.
Zapoznaj się z poniższym artykułem, aby uzyskać instrukcje (przy użyciu ssms i T-sql). Te kroki są przeznaczone dla początkujących z mniejszą znajomością T-SQL.
generuje skrypt dla wszystkich tabel z kluczem podstawowym = bigint, które nie mają zestawu tożsamości; to zwróci listę wygenerowanych skryptów z każdą tabelą;
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;