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.
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?