Creating a Script from a Stored Procedure

A simple task, I thought, but it took me to some interesting places. The method is broadly this:

1) Create an instance of SQL-DMO SQL Server, and use the script method to save the create table text in a file.
2) Get the text from the file into a sp variable.
3) Delete the text file.

Here are the details of the method, and a summary which puts it all together:

1) Create an instance of SQL-DMO SQL Server, and use the script method to save the create table text in a file.  Here's the usage:

exec run_script 'my_server', 'my_database', 'my_table', 74077, 'my_path_name'

And here's the sp...

CREATE proc run_script
@server varchar(100),
@database_name varchar(100),
@table_name varchar(100),
@script_id int,
@path_name varchar(200) as

--runs a sql server script and outputs it to a file.

declare @i int
declare @object int
declare @return varchar(200)
declare @q varchar(200)
declare @is_error bit

set @is_error = 0

--create sql server object
EXEC @i = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

--connect to sql server using windows nt and verify the connection EXEC
@i = sp_OASetProperty @object, 'LoginSecure', 1
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object
EXEC @i = sp_OAMethod @object, 'Connect', NULL, @server
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object
EXEC @i = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

--run the script
SET @q = 'Databases("' + @database_name + '").Tables("' + @table_name
+
'").Script(' + cast(@script_id as varchar(10)) + ', ' + @path_name +
')'
IF NOT @i = 0 begin EXEC sp_OAGetErrorInfo @object set @is_error = 1
end
EXEC @i = sp_OAMethod @object, @q, @return OUT
IF NOT @i = 0 begin EXEC sp_OAGetErrorInfo @object set @is_error = 1
end

--destroy sql server object
EXEC @i = sp_OADestroy @object
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

return @is_error
GO

2) Get the text from the file into a sp variable. My first try was to use the FileSystemObject...

CREATE proc get_from_file @file_output varchar(8000) output,
@path_name
varchar(200) as
--outputs all the text of a file concatenated into a single string.
--Note - 255 character limitation.

DECLARE @file_output varchar(8000)
DECLARE @fso int
DECLARE @ts int
DECLARE @i int

EXEC @i = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso
EXEC @i = sp_OAMethod @fso, 'OpenTextFile', @ts out, @path_name
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso
EXEC @i = sp_OAMethod @ts, 'ReadAll', @file_output out
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @ts
EXEC @i = sp_OADestroy @ts
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @ts
EXEC @i = sp_OADestroy @fso
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso
GO

This, however, has a 255 character limitation - so it was back to the drawing board. I don't much like it, but I came up with this...

declare @file_output varchar(8000)
exec get_from_file @file_output output, 'my_path_name'
select @file_output

And here's the sp (with a simple supporting sp below it)...

CREATE proc get_from_file @file_output varchar(8000) output,
@path_name
varchar(200) as
--outputs all the text of a file concatenated into a single string.

set nocount on

--get_unique_name for temporary table
declare @unique_table_name varchar(100)
exec get_unique_name @unique_table_name output
set @unique_table_name = '##' + @unique_table_name

--create concatenated string and puts it into the table
exec('
create table #t1 (c1 varchar(8000))
bulk insert #t1 from ''' + @path_name + '''
declare @s varchar(8000)
set @s = ''''
select @s = @s + isnull(c1, '''') + char(13) from #t1
select c1 = @s into ' + @unique_table_name
)

--output the single value in the table to our output variable
declare @q nvarchar(100)
set @q = 'select @p1 = c1 from ' + @unique_table_name
exec sp_executesql @q, N'@P1 varchar(8000) output', @file_output
output

--drop our temporary table
exec ('drop table ' + @unique_table_name)

set nocount off
GO

Supporting sp...

CREATE proc get_unique_name @output varchar(50) output as

--outputs a unique name based on the current user and the precise time the sp is run.
--can be used for table names / file names etc.

select @output =
replace(system_user, '\', '_') + '_' +
cast(datepart(yyyy, getdate()) as varchar(4)) + '_' +
cast(datepart(mm, getdate()) as varchar(2)) + '_' +
cast(datepart(dd, getdate()) as varchar(2)) + '_' +
cast(datepart(hh, getdate()) as varchar(2)) + '_' +
cast(datepart(mi, getdate()) as varchar(2)) + '_' +
cast(datepart(ss, getdate()) as varchar(2)) + '_' +
cast(datepart(ms, getdate()) as varchar(3))
GO

3) Delete the text file. This uses a familiar method. This time there are no limitations. Here's the usage...

exec delete_file 'my_path_name'

And here's the sp...

CREATE proc delete_file @path_name varchar(200) as
--deletes a file

DECLARE @object int
DECLARE @i int

EXEC @i = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object
EXEC @i = sp_OAMethod @object, 'DeleteFile', null, @FileSpec =
@path_name
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object
EXEC @i = sp_OADestroy @object
IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object
GO

Putting it all together - here's the usage...

declare @object_text varchar(8000)
exec get_create_table_script @object_text output, 'my_server',
'my_database', 'my_table'
select @object_text

And here's the sp...

CREATE proc get_create_table_script
@create_table_script varchar(8000) output,
@server varchar(100),
@database_name varchar(100),
@table_name varchar(100) as

--outputs a create table script for a sql table. To do this, it runs a script to put it into a file, then gets it from the file and deletes the file

declare @return int

--get path name of temporary sql file
declare @path_name varchar(100)
exec get_unique_name @path_name output
set @path_name = '\\' + @server + '\c$\' + @path_name + '.sql'

--create the 'create table' script and put it into sql file
exec @return = run_script @server, @database_name, @table_name, 74077,
@path_name

--return if above step errored.
if @return = 1 return

--get script results from sql file into output variable
exec get_from_file @create_table_script output, @path_name

--delete temporary sql file
exec delete_file @path_name
GO

And there's your final stored procedure which does what we set out to do.