To find these stored procedures, you can spend time searching the internet, looking at some stored procedures that you have not yet known, and perhaps in one or two hours you may find that you want to ... Maybe you're lucky to find that other people in their article lists all the stored procedures, functions, and views, and describes how to use these stored procedures.
But in fact, you can list these stored procedures, functions, and views in less than a minute! This list even includes all of the undocumented stored procedures in SQL Server. With this list, you can identify the stored procedures you want to find.
SQL Server 2005 actually saves a list of all stored procedures, including documented, undocumented, or even user-defined! All of this information is contained in the system tables. The easiest way to do this is to use a system view, especially the sys.all_objects view.
You can also use the Sys.procedures catalog view, but my test results show that this view will filter out some of the stored procedures.
You can also use the System store procedure sp_stored_procedures to return a list of stored procedures in the current environment, but the stored procedure also limits the stored procedure return value.
By contrast, I think: if you want to get all the stored programs in SQL Server 2005, it is recommended that you use the Sys.all_objects system view, sys. Procedures or sp_stored_procedures These two views will filter out some content for some unknown reason, resulting in incomplete information.
Stored procedure information is stored in the system tables in the respective user databases. SQL Server 2005 holds unique identification information for stored procedures, such as the name of the stored procedure, the time it was created, the time it was modified, whether it came from Microsoft, and so on.
How do you make sure that all of your user databases can create these stored procedures automatically?
When the SQL Server deployment is complete, Microsoft provides stored procedures that are saved in the master database. When you create a new database, the master database is used as the template database, so all stored procedures in the master database are automatically created into the database that you created.
If you want to create a stored procedure and want to be able to distribute it automatically to all databases, you can create the stored procedure in the master database so that the newly created database will automatically contain the new stored procedure you create. But for previously existing databases, you still need to manually create this stored procedure in each database.
First, list all the stored procedures
The following code lists all the stored procedures that exist in SQL Server 2005.
Copy Code code as follows:
use [Your_database_name_here];
Go
SELECT * from Sys.all_objects
WHERE ([Type] = ' P ' or [type] = ' X ' or [type] = ' PC ')
Order by [name];
Go
If you have installed SQL Server 2005 and have used all the default settings, you will see a similar interface:
The above command is filtered by the type object. From the table below, you can determine the type of object you want, and then adjust the type in the WHERE clause according to your actual situation.
This list lists only the comments, functions, and procedures.
II. user-defined stored procedures
In daily use, another field, "is_ms_shipped", is often used to filter records. This field is used to determine if the stored procedure is published by Microsoft, and if it was originally published by Microsoft, the value of the field is 0; If it is not published by Microsoft, the value of this field is 1.
Based on the above T-SQL statement, you can simply add a restriction "and [is_ms_shipped] = 0" To find stored procedures that are not created by Microsoft, which may have been created when you or a Third-party software provider accessed the database. If you only want to look at the stored procedures provided by Microsoft, you can only use the value from 0 to 1.
The following example shows how to get a user-defined stored procedure through a T-SQL code.
Copy Code code as follows:
use [Your_database_name_here];
Go
SELECT * from Sys.all_objects
WHERE ([Type] = ' P ' or [type] = ' X ' or [type] = ' PC ')
and [is_ms_shipped] = 0
Order by [name];
Go
Note that the execution of this statement results in a different list of records for each individual's database. Circumstances vary. After execution, you will see an output similar to the following figure.
Third, summary
As you can see from the example above, with a small amount of code, we can easily and quickly get the list of stored procedures for the SQL Server we are using, and you don't have to search for these stored procedures in one Web site. You might learn a complex script that you've spent a lot of time writing, and now you can do the same with a single stored procedure.
Keep in mind that when using a stored procedure that cannot determine the source or result, it should be validated first in the test system; Never use any tests that are not thoroughly tested in a production environment.
This article just tells you what you can do to get all the stored procedures in SQL Server 2005, but it's not encouraging you to use any type of stored procedure at will, especially without document types. Microsoft has repeatedly stated that Microsoft does not provide any support for any document-free stored procedures, extended stored procedures, functions, and views.
