Generate Scripts Result in a order of tables and then stored procedures in SQL Server

Question

Post reply

Generate Scripts Result in a order of tables and then stored procedures in SQL Server

Ramchand

SSC Veteran

Points: 263
More actions
September 10, 2014 at 1:10 am

#290507

Hello All,
I have created one table and one stored procedure for to insert/delete/update the data in that table.
So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.
Generating Scripts:
Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
Thanks,
Ramchand

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

erics44 SSCertifiable Points: 6311 More actions · Answer 1

is it posible to put an if table exists line in your procedure?

Lowell SSC Guru Points: 323518 More actions · Answer 2

well i know how to get the order of dependencies : if you limit the results to just Tables, that is the order of foreign key hierarchys...but then you have to script them in that order, which means you cannot use the GUI, i think...you have to script it yourself, or use powershell and SMO.

the built in procedure sp_msdependencies can give you what you are asking, as far as hieraarchy goes.

note that if you have circular dependancies, sp_msdependencies from MS will hang.

here's a codeblock i use, but i have TSQL scripts to script out tables to my favorite format.

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectID int,

SchemaName varchar(255),

ObjectName varchar(255),

ObjectType varchar(255),

oTYPE int,

SequenceOrder int

)

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (oTYPE,ObjectName,SchemaName,SequenceOrder)

EXEC sp_msdependencies @intrans = 1

UPDATE MyTarget

SET MyTarget.objectID = objz.object_id,

MyTarget.ObjectType = objz.type_desc

FROM #MyObjectHierarchy MyTarget

INNER JOIN sys.objects objz

ON MyTarget.ObjectName = objz.name

AND MyTarget.SchemaName = schema_name(objz.schema_id)

--only tables, isntead of all objects

SELECT * FROM #MyObjectHierarchy

WHERE ObjectType = 'USER_TABLE'

ORDER BY HID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ramchand SSC Veteran Points: 263 More actions · Answer 3

Thanks SSChampion,

The provided script gives me some idea about dependencies.

Thanks,

Ramchand

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

ramchand.repalle (9/10/2014)
Hello All,
I have created one table and one stored procedure for to insert/delete/update the data in that table.
So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.
Generating Scripts:
Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
Thanks,
Ramchand

Stored procedures are not dependent on tables at create time, only run time. The following script works perfectly.

CREATE PROCEDURE DummyTest

AS

SELECT * FROM DummyTable;

GO

CREATE TABLE DummyTable

(ID INT);

GO

EXEC DummyTest

DROP TABLE DummyTable;

DROP PROCEDURE DummyTest;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Ramchand SSC Veteran Points: 263 More actions · Answer 5

Hello SSC Eights,

Thank you very much. I have executed your script it works perfectly fine.

I am about to know from you something as you mentioned in comment like below

"Stored procedures are not dependent on tables at create time, only run time. The following script works perfectly."

Suppose, if i am going to run the below script

CREATE TABLE DummyTable

(ID INT);

GO

CREATE PROCEDURE DummyTest

AS

SELECT ID,Name FROM DummyTable;

GO

EXEC DummyTest

DROP TABLE DummyTable;

DROP PROCEDURE DummyTest;

Now, it thows the error while creating the stored procedure as invalid column ("Name") in the DummyTable.

Can you please give me some more idea about this.

Thanks,

Ramchand.

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

MSDN only refers to tables and does not mention the columns.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure will fail at execution time if the referenced tables do not exist.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]