October 30, 2006 at 2:22 pm
Many thanks in advance.
October 30, 2006 at 2:26 pm
SELECT Name
FROM dbo.SysObjects
WHERE XType = 'P'
AND Status >= 0
ORDER BY Name
October 30, 2006 at 4:55 pm
October 31, 2006 at 2:33 pm
I greatly appreciate your input. INFORMATION_SCHEMA is a view. But could you tell me where I can find it? However, I believe we can only get a list of all stored procedures using your code. In order to get all user-defined stored procedure, we have to use other one.
Many thanks once again.
October 31, 2006 at 2:50 pm
The view in question is located in the master database.
However the information displayed comes from the database from where you call the view.
So if you call the view from pubs, you'll get different results than if you call it from northwind.
If you don't see all the procs using the information_schema views it's because you don't have persmisison on those objects. My select query skips the owner validation part.
Does that clear it up for you?
October 31, 2006 at 2:53 pm
SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY SPECIFIC_NAME
Its the same view, just a different parameter to determine which objects you want to look at.
In SQL 2000 you can find it in the master database (under views of course).
In SQL 2005 you can find it in the database you are looking at in views\system views
October 31, 2006 at 3:19 pm
Thank you so much for you guy's inputs. I forgot INFORMATION_SCHEMA is the owner of the view of ROUTINES. However, only Ninja's code could retrieve a list of all user-defined stored procedure. Notice, I do not want to retrieve all of stored procedures!
October 31, 2006 at 4:02 pm
Can you post some sample data and the required output... without that we can't help you anymore than we already did.
October 31, 2006 at 4:06 pm
Your code did work. I mean your code is the only choice we have at this moment.
Many thanks once again.
November 1, 2006 at 7:29 am
Alright, thanx for clearing this up.
Good luck finishing that project!
June 11, 2021 at 3:49 pm
The best I've designed is below. The reason I specifically called out name is because these are specific system objects that are created t install an not marked as MS Shipped. I could have used '%diagram%' but I would have to worry if anyone in the past or future will call a FCN or SP using that word. Therefore, I called each object specifically so...hopefully the future name won't follow these conventions. There is a likelihood this will happen so periodically reviewing without the filter is important.
select *
from sys.objects
where is_ms_shipped=0
and type not in ('D','U','PK','UQ','SN')
and name not like '%diagram'
and name not like 'sp_help%'
and name not like '%diagrams'
and name not like '%diagramobjects'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply