Czy istnieje sposób na wygenerowanie skryptu tworzenia tabeli w TSQL?

Czy istnieje sposób na wygenerowanie skryptu tworzenia z istniejącej tabeli wyłącznie w języku T-SQL (bez użycia SMO, ponieważ T-SQL nie ma dostępu do SMO). Powiedzmy, że procedura składowana, która otrzymuje nazwę tabeli i zwraca ciąg znaków, który zawiera skrypt tworzenia dla danej tabeli?

Teraz opiszę sytuację, w której się znajduję, ponieważ może istnieć inny sposób podejścia do tego. Mam instancję z kilkadziesiąt baz danych. Te bazy danych mają ten sam schemat, wszystkie tabele, indeks i tak dalej. Zostały one utworzone w ramach instalacji oprogramowania innej firmy. Muszę mieć sposób na współpracę z nimi, aby móc agregować dane z nich w sposób ad hoc. Mili ludzie z dba.se już mi pomogli. Jak utworzyć wyzwalacz w innej bazie danych?

Obecnie muszę znaleźć sposób na dokonanie wyboru z tabeli we wszystkich bazach danych. Zapisałem wszystkie nazwy baz danych w tabeli o nazwie Databaseesi napisałem następujący skrypt do wykonania instrukcji select na wszystkich z nich:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) = 
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

Jednak powyższy skrypt kończy się niepowodzeniem i pojawia się następujący komunikat:

Wyraźną wartość kolumny tożsamości w tabeli „#tmp” można określić tylko wtedy, gdy używana jest lista kolumn, a IDENTITY_INSERT jest włączony.

Dodanie tego:

SET IDENTITY_INSERT #tmp ON

nie pomaga, ponieważ nie mogę określić listy kolumn i zachować jej ogólnego charakteru.

W SQL nie ma możliwości wyłączenia tożsamości w danej tabeli. Możesz tylko upuścić kolumnę i dodać kolumnę, co oczywiście zmienia kolejność kolumn. A jeśli zmieni się kolejność kolumn, musisz ponownie określić listę kolumn, która będzie różna w zależności od tabeli, której dotyczy zapytanie.

Pomyślałem więc, czy mogę uzyskać skrypt tworzenia tabeli w moim kodzie T-SQL, mógłbym nim manipulować za pomocą wyrażeń manipulacji ciągiem, aby usunąć kolumnę tożsamości, a także dodać kolumnę z nazwą bazy danych do zestawu wyników.

Czy ktoś może wymyślić stosunkowo łatwy sposób na osiągnięcie tego, czego chcę?

W 2007 roku poprosiłem o prosty sposób na wygenerowanie CREATE TABLEskryptu za pomocą T-SQL zamiast używania interfejsu użytkownika lub SMO. Zostałem krótko odrzucony .

Jednak SQL Server 2012 sprawia, że ​​jest to bardzo łatwe. Udawajmy, że mamy tabelę z tym samym schematem w wielu bazach danych, np . dbo.whatcha:

CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1), 
  x VARCHAR(MAX), 
  b DECIMAL(10,2), 
  y SYSNAME
);

Poniższy skrypt korzysta z nowej sys.dm_exec_describe_first_results_setfunkcji zarządzania dynamicznego, aby pobrać odpowiednie typy danych dla każdej kolumny (i zignorować IDENTITYwłaściwość). Tworzy potrzebną tabelę #tmp, wstawia z każdej bazy danych na liście, a następnie wybiera z #tmp, wszystko w ramach jednej dynamicznej partii SQL i bez użycia WHILEpętli (to nie poprawia, po prostu łatwiej jest spójrz i pozwala Database_Ref_Nocałkowicie zignorować :-)).

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Wynikowy PRINTwynik:

CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

Kiedy jesteś pewien, że robi to, czego oczekujesz, po prostu odkomentuj EXEC.

(Ufa ci to, że schemat jest taki sam; nie weryfikuje to, że jedna lub więcej tabel zostało zmienionych i może w rezultacie zawieść).

Nie jest możliwe, aby T-SQL wygenerował pełny skrypt tworzenia tabeli. Przynajmniej nie ma żadnej kompilacji. zawsze możesz napisać własny „generator” przeglądający informacje sys.columns.

Ale w twoim przypadku nie potrzebujesz pełnego skryptu tworzenia. Wszystko, czego potrzebujesz, to zapobiec SELECT INTOkopiowaniu właściwości tożsamości. Najłatwiej to zrobić, dodając obliczenia do tej kolumny. Więc zamiast

select * into #tmp from Database1.dbo.Table1 where 1=0

musisz pisać

select id*0 as id, other, column, names into #tmp from Database1.dbo.Table1 where 1=0

Aby wygenerować tę instrukcję, możesz ponownie użyć sys.columns jak w tym SQL Fiddle

Konfiguracja schematu MS SQL Server 2008 :

CREATE TABLE dbo.testtbl(
    id INT IDENTITY(1,1),
    other NVARCHAR(MAX),
    [column] INT,
    [name] INT
);

Dwie kolumny Potrzebujemy namea is_identity: Zapytanie 1 :

SELECT name,is_identity
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

Wyniki :

|   NAME | IS_IDENTITY |
|--------|-------------|
|     id |           1 |
|  other |           0 |
| column |           0 |
|   name |           0 |

Dzięki temu możemy użyć CASEinstrukcji do wygenerowania każdej kolumny dla listy kolumn:

Zapytanie 2 :

SELECT ','+ 
    CASE is_identity
    WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
    ELSE QUOTENAME(name)
    END
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

Wyniki :

|        COLUMN_0 |
|-----------------|
| ,[id]*0 AS [id] |
|        ,[other] |
|       ,[column] |
|         ,[name] |

Przy odrobinie sztuczki XML możemy połączyć to wszystko razem, aby uzyskać pełną listę kolumn:

Zapytanie 3 :

SELECT STUFF((
  SELECT ','+ 
      CASE is_identity
      WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
      ELSE QUOTENAME(name)
      END
    FROM sys.columns
   WHERE object_id = OBJECT_ID('dbo.testtbl')
   ORDER BY column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'')

Wyniki :

|                               COLUMN_0 |
|----------------------------------------|
| [id]*0 AS [id],[other],[column],[name] |

Należy pamiętać, że nie można utworzyć tabeli #temp przy użyciu dynamicznego SQL i używać jej poza tą instrukcją, ponieważ tabela #temp wychodzi poza zakres po zakończeniu dynamicznej instrukcji SQL. Musisz więc wycisnąć cały kod w ten sam dynamiczny ciąg SQL lub użyć prawdziwej tabeli. Jeśli musisz być w stanie wykonać wiele z tych skryptów / procedur w tym samym czasie, musisz nam losową nazwę tabeli, w przeciwnym razie będą się nawzajem nadepnąć. Coś takiego QUOTENAME(N'temp_'+CAST(NEWID() AS NVARCHAR(40))powinno mieć wystarczająco dobre imię.

Zamiast kopiować wszystkie dane, możesz również użyć podobnej techniki, aby po prostu automatycznie wygenerować widok dla każdej tabeli, która łączy wszystkie wcielenia tej tabeli we wszystkich bazach danych. W zależności od wielkości stołu może to być jednak szybsze lub wolniejsze, dlatego należy go przetestować. Jeśli pójdziesz tą drogą, umieściłbym te widoki w osobnej bazie danych.

W tym artykule SQLServerCentral jest dobry skrypt do osiągnięcia tego:

• Uzyskaj DDL dla dowolnej tabeli SQL 2005 (wymagana rejestracja) autorstwa Lowell Izaguirre .

Aktualna najnowsza wersja skryptu jest również dostępna jako tekst tutaj (stormrage.com).

Chciałbym, żeby był tu sposób na dołączenie całego skryptu, ponieważ działa dla mnie. Skrypt jest po prostu za długi, aby go wkleić.

Informacja o prawach autorskich:

--#################################################################################################
-- copyright 2004-2013 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
-- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt
--Purpose: Script Any Table, Temp Table or Object
--
-- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced.The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#################################################################################################

Możesz wygenerować zgrubny CREATE TABLEza pomocą dynamicznego SQL z danych wINFORMATION_SCHEMA.COLUMNS .

Jeśli chcesz dodać ograniczenia itp., Musisz dodać informacje z niektórych innych INFORMATION_SCHEMA widoków.

Dokumentacja Microsoft