Transcript Metadata
Metadata Metadata is information about data or other information. Data Dictionary A Data dictionary may cover the whole organisation, a part of the organisation or a database. In its simplest form, the data dictionary is a collection of data object definitions More advanced data dictionary contains database schemas and entity-relationship models SQL Server metadata Microsoft® SQL Server™ provides two methods for obtaining metadata: •Using system stored procedures •Examining information schema views. These views provide an internal, system table-independent view of the SQL Server metadata. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA. System Stored Procedures sp_tables Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause). e.g. EXEC sp_tables Syntax sp_tables [[@name =] 'name'] [,[@owner =] 'owner'] [,[@qualifier =] 'qualifier'] [,[@type =] "type"] e.g. EXEC sp_tables customer, u878776, Company, “’TABLE'" Returns information about the customer table owned by u878776 in the Company database type could be TABLE, SYSTEM TABLE, and VIEW Result Set Column name TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS Description database name Table owner name Table name. Table, system table, or view. Detail of the numerous stored procedures can be found in the Transact*SQL Help system sp_columns This example returns column information for a specified table. EXEC sp_columns @table_name = 'customers' Syntax sp_columns [@table_name =] object [,[@table_owner =] owner] [,[@table_qualifier =] qualifier] [,[@column_name =] column] [,[@ODBCVer =] ODBCVer] INFORMATION_SCHEMA These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains metadata for all data objects stored in that particular database. · · · · · · · · · · · · · · · · · CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES COLUMNS CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE REFERENTIAL_CONSTRAINTS SCHEMATA TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS INFORMATION_SCHEMA.TABLES Contains one row for each table in the current database for which the current user has permissions. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name. e.g. Select * from information_schema.tables; Column name Description TABLE_CATALOG Table qualifier. TABLE_SCHEMA Table owner. TABLE_NAME Table name. TABLE_TYPE Type of table. Can be VIEW or BASE TABLE. INFORMATION_SCHEMA.VIEWS Contains one row for views accessible to the current user in the current database.. Column name Description TABLE_CATALOG View qualifier. TABLE_SCHEMA View owner. TABLE_NAME View name. VIEW_DEFINITION view definition text. CHECK_OPTION Type of WITH CHECK OPTION. IS_UPDATABLE whether the view is updatable.