Re: find store procedure - Database Discussion Boards

George_George14-Jan-09 4:58

14-Jan-09 4:58

Thanks Paddy!

1.

I find I could only export one store procedure at one time? No quick export command from menu to export all store procedures from a database? Smile | :)

2.

I think half of my question is solved, i.e. how to get the text sql form of store procedure. My second half question is, how to sync code with source control system. I think the solution should be merging the code manually and paste the code into Management Studio, then execute alter store procedure to take effect the new changes? Is that working? Or you have better ideas?

regards,
George

Re: find store procedure

Paddy Boyd14-Jan-09 5:29

Paddy Boyd

14-Jan-09 5:29

re point 2:

Depends on your circumstances - if it's a standalone system and you only have one instance of it, then that sounds fine.

If you need to install this in a number of places, having a little installer application that runs through and executes each of the SQL files is probably a good idea - means you can always be sure that the latest of everything is on the machine.

Also, if you have the definitive version in source control, you can check that nobody's been f*cking around with it.

Re: find store procedure

George_George14-Jan-09 5:55

George_George

14-Jan-09 5:55

Thanks Paddy,

1.

How about your answer to my question #1? Smile | :)

2.

I am interested in the installer solution. But how could I invoke SQL statements (e.g. the statements used to create table/store procedures) from an installer application? My confusion is the instlaler application should only be able to call binaries, but how to call sql statements (text, not binary)?

regards,
George

Re: find store procedure

Wendelius14-Jan-09 3:31

Wendelius

14-Jan-09 3:31

George_George wrote:
Any ideas to find where is the physical location of store procedures

All information concerning a stored procedure is located in system tables. These tables reside in the primary file group of the database. However, the physical location isn't normally relevant for these tables (unless you're interested in complex backup/restore scenarios etc.).

One place you can look at is sys.sql_modules. From that view you can see for example your current procedures and their implementation.

George_George wrote:
I have a source control system and I want to check-in the store procedure code

You can use Management Studio's ability to script a procedure. Using it, you'll get an initial version for your source control. After that, modify the script as you like and check-out/check-in when you make modifications. I'm not aware that Management Studio supports any source control system so you may have to do this using file system operations (meaning you checkout to a file and then open it in Management Studio etc)

George_George wrote:
And I also want to merge the store procedure code outside Management Studio using other diff tools and let Management Studio reflect the changes

As previously use whetever tools you need and open the result into Management Studio.

George_George wrote:
merge/change the code from outside, when I press refresh button in Management Studio, I can see the new merged code of store procedures

You first have to execute the script in order to get the procedure to the database. After that, you'll see the change.

The need to optimize rises from a bad design.My articles[^]

Re: find store procedure

George_George14-Jan-09 4:48

George_George

14-Jan-09 4:48

Thanks Mika!

So many comprehensive comments.

Forgive me that I am a newbie and after reading and following your comments I have some further questions.

1.

"One place you can look at is sys.sql_modules." -- from Management Studio under System Database, I only see 4 database -- master, model, msdb and tempdb. Where could I find the sys.sql_modules as you mentioned?

2.

"You can use Management Studio's ability to script a procedure." -- good point to start! Could you let me know which menu do you mean please (some more steps)? I never did this before. Smile | :)

3.

"As previously use whetever tools you need and open the result into Management Studio."

"You first have to execute the script in order to get the procedure to the database. After that, you'll see the change."

Just confirm your points. My understanding is, I merge the code manually and past the code into Management Studio, then execute alter store procedure to take effect the new changes? Is that what you mean?

regards,
George

Re: find store procedure

Wendelius14-Jan-09 5:12

Wendelius

14-Jan-09 5:12

George_George wrote:
could I find the sys.sql_modules as you mentioned?

It depends on the version of your Management Studio, but most likely if you expand the following tree path, you'll find them: YourDatabase/Views/System Views. There's a bunch of views including sys.sql_modules.

Or another way is simply to execute a select statement:

select * from sys.sql_modules

George_George wrote:
You can use Management Studio's ability to script a procedure

Right click on the procedure and select Script Stored Procedure as/Create To/File... (or some other target). If you want to script everything in the database select the database, right click and Tasks/Generate Scripts...
Try to play around with the Management Studio. It has many helfpul features.

George_George wrote:
My understanding is, I merge the code manually and past the code into Management Studio, then execute alter store procedure to take effect the new changes

Exactly. That replaces the information in the system tables and then you can use your new version of the procedure.

The need to optimize rises from a bad design.My articles[^]

Re: find store procedure

George_George14-Jan-09 5:18

George_George

14-Jan-09 5:18

Thanks Mika,

I have found the store procedures from sys.sql_modules! Cool! But I think using Script Stored Procedure as/Create To/File should be more convenient than using sys.sql_modules to achieve my goal of sync with source control system. Any comments or ideas? Smile | :)

regards,
George

Re: find store procedure

Wendelius14-Jan-09 5:43

Wendelius

14-Jan-09 5:43

Absolutely use scripting abilities provided by Management Studio.

The reason I explained the sys.sql_modules was to give you a basic understanding how SQL Server stores information about database objects in the system tables. This was based on your question "where is the physical location of stored procedures".

System tables are very handy in several situtations but in this particular case scripting using Management Studio is both easier and more reliable.

The need to optimize rises from a bad design.My articles[^]

Re: find store procedure

George_George14-Jan-09 5:59

George_George

14-Jan-09 5:59

Thanks Mika!

I find I could only export one store procedure at one time? No quick export command from menu to export all store procedures from a database?

regards,
George

Re: find store procedure

Ashfield14-Jan-09 21:23

Ashfield

14-Jan-09 21:23

Have a look at my article here[^], it explains the System Management Object and gives you examples of how to generate scripts for all database objects - you may have to tweak it to do exactly what you want, but its a good starting point.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: find store procedure

George_George15-Jan-09 22:59

George_George

15-Jan-09 22:59

Thanks Bob!

Good start point!

regards,
George

Re: find store procedure

J4amieC14-Jan-09 22:37

J4amieC

14-Jan-09 22:37

George_George wrote:
only export one store procedure at one time? No quick export command from menu to export all store procedures from a database

use. your. initiative. we were all newbie's once you know!

Right click your database > Tasks > Generate Scripts...

I assume you can follow a simple wizard, if not im sure you'll head back here asking something benign like "How to I click on OK to proceed?"

Re: find store procedure

George_George15-Jan-09 22:51

George_George

15-Jan-09 22:51

Hi J4amieC,

I did not find an option to script all store procedures. Smile | :)

Any more hints or ideas?

regards,
George

Re: find store procedure

J4amieC16-Jan-09 1:11

J4amieC

16-Jan-09 1:11

So the answer was no, you cant follow a wizard.

Seriously George, change career paths - you're not cut out to be a developer.

Re: find store procedure

Hamid_RT15-Jan-09 21:04

Hamid_RT

15-Jan-09 21:04

See How do I find a stored procedure containing <text>?[^]

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

Re: find store procedure

George_George15-Jan-09 22:49

George_George

15-Jan-09 22:49

Good stuff, thanks Hamid!

regards,
George

smarter ways to make a column value unique

George_George14-Jan-09 2:01

George_George

14-Jan-09 2:01

Hello everyone,

I have a table and a couple of columns in this table. For some specific column, it is not primary key, but I want to accept only unique value for this specific column for each row.

Currently, my naive solution is to use a store procedure to handle table insertion and if in the store procedure I find there is already a row whose existing value for the specific column is the same as the new column value of the new row to be inserted, I will return error from the store procedure and refuse to insert the whole new row.

Any other smarter ways?

thanks in advance,
George

Re: smarter ways to make a column value unique

Colin Angus Mackay14-Jan-09 2:17

Colin Angus Mackay

14-Jan-09 2:17

Have you thought about using a unique constraint[^]?

* Developer Day Scotland 2 - Free community conference
* The Blog of Colin Angus Mackay

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Re: smarter ways to make a column value unique

George_George14-Jan-09 2:30

George_George

14-Jan-09 2:30

Thanks Colin,

Good idea!

regards,
George

Re: smarter ways to make a column value unique

Hamid_RT15-Jan-09 2:28

Hamid_RT

15-Jan-09 2:28

See SQL UNIQUE Constraint[^] and see Adding a unique constraint[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

How to generate XML reoprt programmatically using SQL Server Reporting Services

DipaliG13-Jan-09 18:32

DipaliG

13-Jan-09 18:32

I am new to SQL Server 2005. I need to generate a report in XML format using SSRS 2005. Is it possible by using Rs.exe? Please give me the step-by-step solution for this.

Thanks.

Cross-post

Wendelius13-Jan-09 21:54

Wendelius

13-Jan-09 21:54

Don't cross-post. It's considered rude. Pick a forum and stick with it.

You may find this useful: Reporting Services Tutorials[^].

The need to optimize rises from a bad design.My articles[^]

SQL Server 2005 query execution time

sujithkumarsl13-Jan-09 18:05

sujithkumarsl

13-Jan-09 18:05

hi,
Is there any way to find the execution time of a query? I simply don't want to perform time consuming queries,

My small attempt...

Re: SQL Server 2005 query execution time

RyanEK13-Jan-09 19:05

RyanEK

13-Jan-09 19:05

I'm not sure what you're asking. Are you asking if its possible to know the execution time of a query before you run it?

If you just want to know how long a query took you can use statistics.

<br />
SET STATISTICS TIME ON<br />
-- Query here<br />
SET STATISTICS TIME OFF<br />

Ryan

Re: SQL Server 2005 query execution time

sujithkumarsl13-Jan-09 19:07

sujithkumarsl

13-Jan-09 19:07

yes, i want to know the execution time before i run the query

My small attempt...

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.