![]() modify_date to restrict by the last modified date of the stored procedures (Note that this is a newly added field and cannot be queried in SQL Server 2000).create_date to restrict by the creation date of the stored procedures.type=’P’ to select only Stored Procedures.The optional filters we can use to narrow down the stored procedures we are looking for are: Sys.sql_modules is then joined in via the object_id and we can get the text of the stored procedures listed using the definition column. Sys.objects contains general information such as the name of the stored procedure, the type of object (in this case we are looking for type=’P’ which is for stored procedures), the creation date and the last modification date of the stored procedure. The system tables in SQL Server 2005 & 2008 that we will need to query are sys.objects and sys.sql_modules. However it is important to keep in mind that the system tables of SQL Server 2000 are different from those of SQL Server 20, so different system tables need to be queried. SQL Server 2000, 2005, and 2008 all support queries against the creation date of the procedure, the name of the procedure, and the content of the procedure. Searching through hundreds of procedures through the GUI or scripting all of the procedures and then running a search through the text are not viable options, so a call to the system tables is definitely in order. An example would be that I want to look for all stored procedures that have a ‘like’ where condition and I want to change this to an ‘=’ condition. Specifically, I sometimes want to get a list of stored procedures in a database that contain a specific line of text that I want to update. Rather than running visual searches through the GUI or scripting the stored procedures, the fastest way to find what you are looking for is a direct query via SSMS (SQL Server Management Studio). It is often very useful to be able to query the SQL Server system tables to find data about stored procedures. Query MS SQL Server system tables for stored procedure meta information. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |