T-sql to find names of objects

Question

Post reply

T-sql to find names of objects

SkyBVI

SSCoach

Points: 15128
More actions
September 20, 2010 at 12:50 pm

#228136

Hi,
Is there any T-sql to find out the names of all the system stored procedures in any database??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 11 posts - 1 through 10 (of 10 total)

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

kevin4u06 SSCrazy Points: 2093 More actions · Answer 1

Hi,

This might help you.

USE AdventureWorks;

GO

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

AND name = '<user defined stored procedure>'

GO

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ kevin

It doesnt give any error.

I see there are many system stored procedures.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@ kevin

oops

I meant to say it doesnt give any output.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

System stored procs generally aren't stored in the user databases, they're in the hidden system resource database.

Try this

SELECT *

FROM sys.system_objects

WHERE type = 'P'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ gila

Ya, the code working fine , but if i run that in any user database or system database ,

it is giving the same output

why?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

As I said...

GilaMonster (9/20/2010)
System stored procs generally aren't stored in the user databases, they're in the hidden system resource database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ gila

I wanted to say that

if there are few user dbs and system databases

each having different system stored procs...

then this above query will give all the system stored procs in all databases??

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

No. In most cases system stored procedures are not stored in user databases. They're all stored in a hidden database called the system resource database. So procedures like sp_help, sp_who, sp_execute, etc are not stored in the user databases at all.

That query returns the system procs that are stored in the system resource database.

If you want system procs in the user databases, use the below code, but in the vast majority of cases it will return no rows.

SELECT *

FROM sys.procedures

WHERE is_ms_shipped = 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Perhaps you could explain what you are trying to do or why you want this? We might be able to help, or let you know if you might be viewing things incorrectly.

SkyBVI SSCoach Points: 15128 More actions · Answer 10

@ steve

The main purpose is that, I am creating a documentation (excel file) which has name of all the objects like tables, procedures, sys stored procs, triggers in all databases along with their owner name and creation date.

So, I am thinking of creating query and exporting the results to a excel file.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP