Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating a Script from a Stored Procedure

By Ryan Randall,

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.

Total article views: 16465 | Views in the last 30 days: 14
 
Related Articles
ARTICLE

Creating Dynamic Outputs in SSIS

In this article, we'll see how you can use the Foreach loop and the Expression Language to create mu...

FORUM

How to see & output data > 8192 in varchar(max)

How to see & output data > 8192 in varchar(max)

FORUM

Table variable as Output Parameter

Can I declare table variable as output parameter in stored procedure?

FORUM

Different Behaviour of VARCHAR and NVARCHAR

Variables declared with VARCHAR and NVARCHAR were behaviouring differently.

FORUM

Disable IF EXISTS.....DROP parameter when using sp_OA..... stored procs to output objects to txt

How do you disable the IF EXISTS....DROP part when using sp_OA.... stored procs to output objects to...

Tags
miscellaneous    
programming    
sql-dmo    
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones