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

Some time ago, the boss gave a requirement that some tables should be sharded. Therefore, the stored procedure, table names in views and functions in the original database should also be updated, at the beginning, I tried to manually check several stored procedures and found that there were too many stored procedures, which were very inefficient and prone to errors. Besides, there were views and functions, so I thought of the SQL Server built-in directory view and found the solution:

 Use  Reportserver $ sqlserver  Select A. Name,. [  Type  ] , B. [  Definition  ]   From  SYS. all_objects A, SYS. SQL _modules B  Where A. is_ms_shipped=  0   And A. Object_id   = B. Object_id And. [  Type  ]   In ( '  P  ' , '  V  ' ,'  AF  '  )  Order   By A. [  Name  ]   ASC 

From the preceding SQL statement, we can see that two systems are used. all_objects and SYS. SQL _modules two system stored procedures, where sys. all_objects is the System View in SQL Server 2012. In earlier SQL Server versions, sys. objects, at the same time, sys. objects can also be used in 2012, but considering the compatibility in the future, it is better to use a new name in the new version. SYS. all_objects (SYS. Objects ) This view is well-known and frequently used, mainly because : Display the Union of user-defined objects and system objects within the scope of all Architectures   Main fields:   1. Name: Object Name 2. object_id: the Object ID, which is unique in Data 3. principal_id: schema owner ID 4. parent_object_id: ID of the object to which this object belongs, 0 = not a sub-Object 5. Type: object type. Common types include: AF = Aggregate functions. P = SQL stored procedure V = View Tt = table Type U = TABLE (user-defined type) 6. type_desc: Description of Object Types 7. create_date/modify_date: creation date/modification date 8. is_ms_shipped: whether to create an object for internal SQL server. It is often used to determine whether it is a built-in or custom object.   SYS. SQL _modules   Msdn: Return a line for each module object defined in the SQL language. Objects of the P, RF, V, TR, FN, if, TF, and r types are associated with SQL modules. This view is not very common, but needs to be used to return the creation Information of some objects, such as the structure and fields of a table, this view will be introduced later after the field is introduced. Note that the view has the current dB scope and is not the current server. Therefore, before using the view, pay attention to the information added with use dbname.   Main fields:   1. Object_id: ID of an object, which is unique in data. 2. Definition: SQL text used to define this module   In fact, as long as you operate it yourself, it is easy to understand, The content in definition is the same as that in the Middle operation:  

 

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

 

RelatedArticle:

Search for and optimize SQL statements using SYS. dm_exec_query_stats

Use SYS. sysprocesses to check the blocking and deadlock of SQL Server

SQL Server System Table analysis (1)-Backup table

SQL Server System Table Analysis (2)-job table

SQL Server System Stored Procedure Analysis 1-directory Stored Procedure