Stored Procedure
-
Hi, Im new to Stored Procedures and was wondering if there was answer to my question. I have created about 5 stored procedures in SQL Enterprise Manager under "Stored Procedure". One of the parameters is sp_name. Is there a way I can populate a list of the stored procedure names in a table without manually doing so. The idea being is that if the stored_procedures are updated - the table will reflect the changes without having to be manually corrected. Thanking you in advance.
-
Hi, Im new to Stored Procedures and was wondering if there was answer to my question. I have created about 5 stored procedures in SQL Enterprise Manager under "Stored Procedure". One of the parameters is sp_name. Is there a way I can populate a list of the stored procedure names in a table without manually doing so. The idea being is that if the stored_procedures are updated - the table will reflect the changes without having to be manually corrected. Thanking you in advance.
Try:
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1
-
Try:
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1
I think it's safer to use the (documented) INFORMATION_SCHEMA views:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
I think it's safer to use the (documented) INFORMATION_SCHEMA views:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
Grimolfr wrote: I think it's safer to use the (documented) INFORMATION_SCHEMA views Or the equally documented sysobjects. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp[^] And quite frankly, after using the INFORMATION_SCHEMA views earlier this year... Or should I say attempt to use them. I threw them out and refuse to use them until Microsoft come up with something that is actually useful. Have you actually looked at the source for the views - What a load of complete rubbish! For example, this is part of the SELECT clause from the ROUTINES view:
SPECIFIC\_CATALOG = db\_name(), SPECIFIC\_SCHEMA = user\_name(o.uid), SPECIFIC\_NAME = o.name, ROUTINE\_CATALOG = db\_name(), ROUTINE\_SCHEMA = user\_name(o.uid), ROUTINE\_NAME = o.name,Why have SPECIFIC_CATALOG and ROUTINE_CATALOG? They are the same thing. Why have SPECIFIC_SCHEMA and ROUTINE_SCHEMA? They are also the same thing. Why have SPECIFIC_NAME and ROUTINE_NAME? It is just wasting bandwidth and processing time repeating the same data again and again.
-
Grimolfr wrote: I think it's safer to use the (documented) INFORMATION_SCHEMA views Or the equally documented sysobjects. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-o_4zll.asp[^] And quite frankly, after using the INFORMATION_SCHEMA views earlier this year... Or should I say attempt to use them. I threw them out and refuse to use them until Microsoft come up with something that is actually useful. Have you actually looked at the source for the views - What a load of complete rubbish! For example, this is part of the SELECT clause from the ROUTINES view:
SPECIFIC\_CATALOG = db\_name(), SPECIFIC\_SCHEMA = user\_name(o.uid), SPECIFIC\_NAME = o.name, ROUTINE\_CATALOG = db\_name(), ROUTINE\_SCHEMA = user\_name(o.uid), ROUTINE\_NAME = o.name,Why have SPECIFIC_CATALOG and ROUTINE_CATALOG? They are the same thing. Why have SPECIFIC_SCHEMA and ROUTINE_SCHEMA? They are also the same thing. Why have SPECIFIC_NAME and ROUTINE_NAME? It is just wasting bandwidth and processing time repeating the same data again and again.
Because they may not be the same thing in, say, SQL Server 2005 (don't know, haven't bothered to fool with it yet), or the first service pack for SQL2k5. When I tried to go to your documentation link, I got:
Page Cannot Be Found
We apologize for the inconvenience, but the page you are seeking cannot be found in this location. My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. The schema views aren't.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
Because they may not be the same thing in, say, SQL Server 2005 (don't know, haven't bothered to fool with it yet), or the first service pack for SQL2k5. When I tried to go to your documentation link, I got:
Page Cannot Be Found
We apologize for the inconvenience, but the page you are seeking cannot be found in this location. My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. The schema views aren't.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
Grimolfr wrote: When I tried to go to your documentation link, I got: Page Cannot Be Found Don't worry, you can find it in the SQL Server books online that come with SQL Server. In the index type
sysobjects. Grimolfr wrote: My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. That's fine if you can put up with the limitations of the views. But I found them to be very restrictive and they do not return full information.
-
Grimolfr wrote: When I tried to go to your documentation link, I got: Page Cannot Be Found Don't worry, you can find it in the SQL Server books online that come with SQL Server. In the index type
sysobjects. Grimolfr wrote: My original point being that the system tables are subject to completely change their function, schema, usage, etc from one service pack to the next. That's fine if you can put up with the limitations of the views. But I found them to be very restrictive and they do not return full information.
"Try: select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 " Worked like a treat - thanks for your help and all the associated input