Shulou(Shulou.com)06/01 Report--

Quoted from "SQL Server 2012 Internals"

"

As views, these metadata objects are based on an underlying Transact-SQL (T-SQL) defnition. The most straightforward way to see the defnition of these views is 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.system_sql_modules.) So to see the defnition of sys.tables, you can execute the following:

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

"

Method 1:

OBJECT_DEFINITION

Returns the defined Transact-SQL source text for the specified object.

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

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

Method 2:

Sp_helptext

Displays definitions of user-defined rules, default values, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, calculated 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 3:

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 associated SQL modules. To identify the containing object, you can connect the view to the 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 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_modules m inner join sys.system_objects t on m.object_id=t.object_id where type='V' and name='systypes'

Sys.sql_modules

A row is returned for each module object defined by the SQL language. Objects of type P, RF, V, TR, FN, IF, TF, and R all have associated SQL modules. In this view, the independent default value, that is, objects of type D also have a SQL module definition. For a description of these types, see the type column 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 ON 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 4:

INFORMATION_SCHEMA

The information schema view is one of several ways to obtain metadata provided by SQL Server. The information schema view provides an internal SQL Server metadata view independent of system tables. Although important changes have been made to the underlying system tables, the information schema view still makes the application work properly. 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 called INFORMATION_SCHEMA. This schema is included 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 SQL Server names and SQL standard names.

SQL Server name

Corresponding SQL standard equivalent name

Database

Catalogue

Architecture

Architecture

Object

Object

User-defined data type

Domain

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

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULTFROM AdventureWorks2014.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = nasty Productions; 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. Columns that describe the return value apply 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 creates the function or stored procedure. These source statements may contain embedded carriage returns. If you return this column to an application that displays the results in text format, the embedded carriage return in the ROUTINE_DEFINITION result may affect the format of the entire result set. If you select the ROUTINE_DEFINITION column, you must adjust the embedded carriage return, for example, to return the result set to a grid or to return the 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 of the function or stored procedure. Otherwise, return NULL.

To ensure a complete definition, query the definition column in the OBJECT_DEFINITION function or in the 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

Quoted from "SQL Server 2012 Internals"

"

Information schema views

Information schema views, 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 2012 comply with the SQL-92 standard, and all these views are in a schema called INFORMATION_SCHEMA. Some information available through the catalog views is available through the information schema views, and if you need to write a portable application that accesses the metadata, you should consider using these objects. However, the information schema views show only objects compatible with 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 are an implementation detail.) If your code doesn't need to

Be strictly portable, or if you need metadata about nonstandard features such as indexes, flegroups, the CLR, and SQL Server Service Broker, using the Microsoft-supplied catalog views is suggested. Most examples in the documentation, as well as in this and other reference books, are based on the catalog view interface.

"

INFORMATION_SCHEMA Tables in MySQL

INFORMATION_SCHEMA provides 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 that are sometimes used for this information are data dictionary and system catalog.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.