Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?
Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?
Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees
and I wrote the following script to execute a select statement on all of them:
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
However the script above fails with the following message:
An explicit value for the identity column in table '#tmp' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Adding this:
SET IDENTITY_INSERT #tmp ON
does not help, since, I can't specify the column list and keep it generic.
In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.
So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.
Can anyone think of a relatively easy way to achieve what I want?
Best Answer
Back in 2007, I asked for an easy way to generate a
CREATE TABLE
script via T-SQL rather than using the UI or SMO. I was summarily rejected.However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g.
dbo.whatcha
:The following script uses the new
sys.dm_exec_describe_first_results_set
dynamic management function to retrieve the proper data types for each of the columns (and ignoring theIDENTITY
property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using aWHILE
loop (that doesn't make it better, just simpler to look at and allows you to ignoreDatabase_Ref_No
entirely :-)).The resulting
PRINT
output:When you are confident it's doing what you expect, just uncomment the
EXEC
.(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)