SQL Server View the location and content of all stored procedures or views

Some time ago, there is a requirement, that is, some of the tables are divided, so the original library stored procedures, views and functions in the table name and other information should be updated, just beginning to try to manually check a few stored procedures, you can find too many stored procedures, check efficiency is very low, but also prone to error, and there are views and functions, So I thought of the SQL Server built-in catalog view, looking for a bit, sure enough to find a solution:

UseReportserver$sqlserverSelect A.name,a.[Type],b.[Definition]FromSys.all_objects A,sys.sql_modules bwhere a.is_ms_shipped=0and a.object_id = b.< Span style= "color: #ff00ff;" >object_id and A.[typein ( p ',  ' v ' 

As can be seen from the SQL statement above, the main use of the two sys.all_objects and sys.sql_modules two system stored procedures, where sys.all_objects is the SQL Server 2012 version of the system view, in the earlier SQL In the server version, sys.objects should be used, and sys.objects is also available in 2012, except for subsequent compatibility, in the new version it is better to use the new name.sys.all_objects (sys.objects)The view is well known and is often used, mainly: Displays the UNION of all schema-scoped user-defined objects and system objectsMain field: 1. Name: Object Name 2. OBJECT_ID: Object identification number, which is unique in the data 3. PRINCIPAL_ID: Schema owner ID4. PARENT_OBJECT_ID: id,0 = is not a sub-object 5 of the object to which this object belongs. Type: Object type, common type has, AF = aggregate function P = SQL stored Procedure V = view TT = Table type U = table (user-defined type) 6. Type_desc: Description of the object Type 7. Create_date/modify_date: Date created/modified 8. Is_ms_shipped: Whether an object created by an internal SQL Server build is commonly used to determine whether a system built-in or user-defined objectsys.sql_modulesMSDN: Returns a row for module objects that are defined for each SQL language. The objects of type P, RF, V, TR, FN, IF, TF, and R all have an associated SQL module. The view is not too common, but to return information about the creation of some objects, such as the schema of a table, fields, and so on, you need to use this view, and later in the introduction of the field, the view will be introduced. Note that the scope of the view is the current DB, not the current server, so before using, be sure to pay attention to the information in front of the Use DBName main field: 1. OBJECT_ID: Object identification number, which is unique in the data 2. Definition: The SQL text used to define this module in fact, as long as you do it in person, it is easy to understand that the content in definition is the same as in the operation:

This article starts the blog park, the original address: SQL Server View the location and contents of all stored procedures or views-http://www.cnblogs.com/xunziji/archive/2012/08/22/2650822.html

Related articles:

Using Sys.dm_exec_query_stats to find and optimize SQL statements

Check SQL Server for blocking and deadlocks with sys.sysprocesses

SQL Server system table Analysis (1)-Backup table

SQL Server system table Analysis (2)-Job table

SQL Server System Stored procedure Analysis 1-Catalog stored procedures

SQL Server View the location and content of all stored procedures or views