Gets the definition text for creating the SQL Server object

Source: Internet
Author: User

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:


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

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


Method Two:


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


Method Three:


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



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 '


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



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


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


Method Four:


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







User-defined data types


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


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.


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% ';


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.


This article is from the SQL Server deep Dive blog, so be sure to keep this source

Gets the definition text for creating the SQL Server object

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.