Gets the definition text for creating the SQL Server object

Referenced from SQL Server internals

“
As views, these metadata objects is based on an underlying Transact-SQL (T-SQL) defnition. The most straightforward-of-the-defnition of these views are by using the Object_defnition function. (You can also see the defnition of these system views by using sp_helptext or by selecting from the catalog view Sys.syste M_sql_modules.) The defnition of Sys.tables, you can execute the following:

SELECT object_definition (object_id (' sys.tables '));
”

Method One:

Object_definition

Returns the Transact-SQL source text for the definition of the specified object.

SELECT object_definition (object_id (' sys.tables '));

Https://msdn.microsoft.com/zh-cn/library/ms176090.aspx

Method Two:

Sp_helptext

Displays definitions of user-defined rules, default values, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraints, views, or system objects such as system stored procedures.

EXEC sp_helptext ' sys.tables '; GO

Https://msdn.microsoft.com/zh-cn/library/ms176112.aspx

Method Three:

Sys.system_sql_modules

Returns a row for each system object that contains the SQL language definition module. System objects of type FN, IF, P, PC, TF, and V have an associated SQL module. To identify the containing object, you can join the view to sys.system_objects.

SELECT ssm.object_id, object_name (ssm.object_id) as object_name, Schema_name (t.schema_id) as Schema_name, T.type, T.type_desc, ssm.definition from Sys.system_sql_modules SSM INNER JOIN Sys.system _objects T on ssm.object_id = t.object_id WHERE t.type= ' P ' GO

Https://msdn.microsoft.com/zh-cn/library/ms188034.aspx

Example:

Select object_name (m.object_id) as name, * from sys.system_sql_modules m inner join sys.system_objects T on m.object_id =t.object_id where type= ' P ' and name= ' sp_renamedb ' select object_name (m.object_id) as name, * from Sys.system_sql_module S M inner join sys.system_objects T on m.object_id=t.object_id where type= ' V ' and name= ' systypes '

Sys.sql_modules

Returns a row for the module object defined for each SQL language. The objects of type P, RF, V, TR, FN, IF, TF, and R all have an associated SQL module. In this view, the independent default value, the object of type D, also has the SQL module definition. For descriptions of these types, see type columns in the sys.objects catalog view.

SELECT sm.object_id, object_name (sm.object_id) as object_name, Schema_name (o.schema_id) as Schema_name, O.type, O.type_desc, sm.definition from sys.sql_modules as SM INNER JOIN sys.objects as o O    N sm.object_id = o.object_id ORDER by O.type; GO

Https://technet.microsoft.com/zh-cn/library/ms175081.aspx

Example:

SELECT object_name (object_id) from sys.sql_modules WHERE objectproperty (object_id, ' isprocedure ') = 1 and definition like '%yourtext% '

Sys.all_sql_modules

Returns the union of Sys.sql_modules and Sys.system_sql_modules.

Https://msdn.microsoft.com/zh-cn/library/ms184389.aspx

Method Four:

Information_schema

The information Schema view is one of several ways that SQL Server provides access to metadata. The Information Schema view provides an internal SQL Server metadata View independent of the system tables. Although important modifications have been made to the underlying system tables, the information Schema view can still make the application work. The information Schema View contained in SQL Server conforms to the information schema definition in the ISO standard.

SQL Server supports three-part naming conventions when referencing the current server. The ISO standard also supports three-part naming conventions. However, the names used in the two naming conventions are not the same. The information Schema view is defined in a special schema named Information_schema. This schema is contained in each database. Each information Schema view contains metadata for all data objects stored in a particular database.

The following table shows the relationship between the SQL Server name and the SQL standard name.

SQL Server name	Corresponding SQL standard equivalent name
Database	Directory
Architecture	Architecture
Object	Object
User-defined data types	Domain

When referencing the information Schema view, you must use a qualified name that contains the INFORMATION_SCHEMA schema name.

SELECT Table_catalog, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, Column_defaultfrom adventureworks2014.information_ SCHEMA.    COLUMNS WHERE table_name = N ' Product '; GO

Https://msdn.microsoft.com/zh-cn/library/ms186778.aspx

Information_schema. ROUTINES

Returns a row for each stored procedure and function in the current database that can be accessed by the current user. The column that describes the return value applies only to functions. For stored procedures, these columns will be NULL.

To retrieve information from these views, specify the fully qualified name of the Information_schema.view_name.

Routine_definition

nvarchar (4000)
column contains the source statement that created the function or stored procedure. These source statements may contain embedded carriage return characters. If you return this column to an application that displays the results in text format, the embedded carriage return character in the routine_definition result may affect the formatting of the entire result set. If you select the Routine_definition column, you must adjust the inline carriage returns, for example, to return the result set to a grid or to return routine_definition to its own text box.

If the function or stored procedure is not encrypted, returns the first 4000 characters of the definition text for the function or stored procedure. Otherwise, NULL is returned.

To ensure that you have a complete definition, query the definition column in the object_definition function or sys.sql_modules catalog view.

SELECT Specific_catalog,specific_schema,specific_name,routine_type from INFORMATION_SCHEMA. ROUTINES WHERE routine_definition like '%yourtext% ';

Https://msdn.microsoft.com/zh-cn/library/ms188757.aspx

Referenced from SQL Server internals

“
Information Schema views
Information Schema View, introduced in SQL Server 7.0, were the original system table-independent view of the SQL Server Metadata.The information schema views included in SQL Server, comply with the SQL-92 standard, and all these views is in a schema called INFORMATION_SCHEMA. Some information available through the catalog views are available through the information schema views, and if you need to Write a portable application that accesses the metadata, and you should consider using these objects.However, the information schema views show is only objects compatible and the SQL-92 standard. This means no information Schema view exists for certain features, such as indexes, which aren ' t defned in the standard. (Indexes is an implementation detail.) If your code doesn ' t need to
Be strictly portable, or if your need metadata about nonstandard features such as indexes, Flegroups, the CLR, and SQL Serv Er Service Broker, using the Microsoft-supplied catalog views is suggested. Most of the examples in the documentation, as well as a and other reference books, is based on the catalog view interface.
”

Information_schema Tables in MySQL

INFORMATION_SCHEMAProvides access to database metadata, information about the MySQL server such as the name of a database or table, The data type of a column, or access privileges. Other terms is sometimes used for this information is data dictionary and system catalog.

Http://dev.mysql.com/doc/refman/5.7/en/information-schema.html

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1846863

Gets the definition text for creating the SQL Server object