How do you spot a user created SP in MSSQL?

[Neil Toulouse]

Hi there!

I am trying to find a way of quickly identifying which of the stored procedures are user created (this is for my VFP upsizing wizard).

I have had a look at the SYSOBJECTS table with:

SELECT * FROM dbo.sysobjects where type = 'P'

...and the only difference I can see between a user and system created SP is that the field base_schema_ver has a zero value if it is user created.

I can therefore use this to spot user created SP's, ie:

SELECT * FROM dbo.sysobjects where type = 'P' and base_schema_ver = 0

..but only if this value doesn't change!

Could anyone tell me if this will be sufficient for my needs, or is there a better way to achieve what I am after?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

Don't system stored procs all begin with sp_? If so, that's probably a safer way of filtering them out.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

Hi Mike!

Unfortunately not all begin with SP, I have some here that begin with DT_ (eg dt_addtosourcecontrol).

At the moment it's not too much of an issue, and it has just raised another thought in my head that I shouldn't be doing it anyway!! Our customers may have added their own for whatever reason so I shouldn't be deleting all user ones willy-nilly!

I am going to explicitly delete the ones we add instead and keep track of those in a table! Oh well, just trying to be lazy... ;-)


Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

Ah, yes. I'd forgotten about those DT_ cases.

Sounds like your idea of tracking your own procs in a table is the way to go.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jrajesh]

Neil,
In my db's sysobjects, all user-created SPs. have their category column populated with 0 and the system SP with 2.

The SQL Server BOL mentions that the category column is 'Used for publication, constraints, and identity.'

SELECT * FROM dbo.sysobjects where type = 'P' and category=0

or perhaps,

SELECT * FROM dbo.sysobjects where type = 'P' and category<>2

???

Rajesh

 

[Neil Toulouse]

Hi Rajesh!

Thanks for the info!

Neil

I like work. It fascinates me. I can sit and look at it for hours...