Several ways to get SQL Server database meta data _mssql

Introduction to meta-data

The most common definition of metadata (metadata) is "structural data about data," or, more simply, "information about data," which can be viewed as metadata in everyday life, such as illustrations, library catalogs, and business cards. In relational database management system (DBMS), metadata describes the structure and significance of data. For example, when managing, maintaining SQL Server, or developing database applications, we often get information about the database schema:

The number and name of tables and views in a database;
The number of columns in a table or view and the name, data type, length, precision, description, etc. of each column;
A constraint defined on a table;
The index defined on a table and the primary key/foreign key information.

Here are a few ways to get metadata.

Get Meta data

Accessing meta data using system stored procedures and system functions

The most common way to get metadata is to use the system stored procedures and system functions provided by SQL Server.

The system stored procedure and the system function provide an abstraction layer between the system table and the metadata, so that we can get the metadata of the current database object without querying the system table directly.

Common metadata-related system stored procedures are as follows:

System stored Procedures

Sp_columns returns the details of the columns for the specified table or view.
Sp_databases returns basic information for all databases on the current server.
Sp_fkeys If the parameter is a table with a primary key, all tables containing the foreign key to the table are returned, and all tables associated with the foreign key/foreign key relationship are returned if the parameter is a table name with a foreign key.
Sp_pkeys returns the primary key information for the specified table.
Sp_server_info returns the various attributes of the current server and their corresponding values.
Sp_sproc_columns returns information about the input and output parameters of the specified stored procedure.
Sp_statistics returns all indexes on the specified table or indexed view, as well as information about the statistics.
Sp_stored_procedures returns a list of stored procedures for the current database, including system stored procedures.
Sp_tables returns all tables and views of the current database, including system tables.

Some of the most common metadata-related system functions are as follows:

System functions

ColumnProperty returns information about a column or procedure parameter, such as whether to allow null values, computed columns, and so on.
Col_length returns the specified property value for the specified database, such as whether it is in read-only mode.
DATABASEPROPERTYEX returns the current settings for the specified option or property of the specified database, such as the state of the database, the recovery model, and so on.
OBJECT_ID returns the identification number of the specified database object name
object_name returns the object name of the specified database object identification number.
OBJECTPROPERTY returns information about the specified database object identification number, such as whether it is a table, whether it is a constraint, and so on.
Fn_listextendedproperty returns extended property values for database objects, such as Object description, formatting rules, input masks, and so on.

Since we cannot directly take advantage of the return results of stored procedures and functions, we use system stored procedures and system functions to query metadata only if we are concerned only with the results of the query, and we do not need to make further use of these results.

For example, if you want to get the basic information for all the databases on the current server, we can run it in the Query Analyzer:

EXEC sp_databases
Go

We can see the name, size, and notes of the database in the return result.

But if you want to refer to this part of the information, or store this part of information for later use, then we must use the middle table to complete this operation:

CREATE TABLE #sp_result
(
database_name sysname,
database_size int,
remarks varchar (254) NULL
)
Go
INSERT into #sp_result
EXEC (' sp_databases ')
go

Accessing meta data using information Schema View

The information Schema view is based on the definition of the Schema view in the SQL-92 standard, which is independent of the system tables and provides an internal view of SQL Server metadata. The great advantage of the information architecture view is that even if we make significant changes to the system tables, the application can use these views normally for access. So for an application, the use of an information Schema view can always work as long as it is a database system that conforms to the SQL-92 standard.

Information Schema View

Information_schema. Check_constraints: Returns information about a column or procedure parameter, such as whether to allow null values, computed columns, and so on.
Information_schema. COLUMNS: Returns all columns and their basic information that the current user can access in the current database.
Information_schema. Constraint_column_usage: Returns all columns with constraints defined in the current database and their constraint names.
Information_schema. Constraint_table_usage: Returns all tables with constraints defined in the current database and their constraint names.
Information_schema. Key_column_usage: Returns all columns in the current database as primary key/foreign KEY constraints.
Information_schema. Schemata: Returns all databases and their basic information for which the current user has permissions.
Information_schema. Tables: Returns all tables or views and their basic information in the current database to which the current user has permissions.
Information_schema. Views: Returns information such as the view that the current user in the current database can access and its owner, definition, and so on.

Because these information architectures are all in the form of views, we can easily get and take advantage of the information we need.

For example, to get how many columns a table has, you can use the following statement:

SELECT COUNT (*) from INFORMATION_SCHEMA. COLUMNS

WHERE table_name= ' mytable '

Accessing meta data using system tables

Although the use of system stored procedures, system functions and information architecture view can provide us with a wealth of metadata information, but for some special metadata information, we still need to directly query the system table. Because SQL Server holds information for all database objects in the system tables, and as a management and developer of SQL Server, understanding the role of each system table will help us understand how SQL Server works inside.

There are a number of system tables in SQL Server, and the most common tables related to metadata queries are as follows:
syscolumns stores information about each column in each table and view, as well as information about each parameter in the stored procedure.
syscomments stores the original SQL text statements that contain each view, rule, default value, Trigger, CHECK constraint, defaults constraint, and stored procedure.
Sysconstraints stores the basic information for each constraint in the current database.
sysdatabases stores the basic information for each database on the current server.
sysindexes stores information for each index in the current database.
Sysobjects the basic information for each object (constraint, default value, log, rule, stored procedure, and so on) that is stored in the database.
Sysreferences stores all columns that include the FOREIGN KEY constraint.
Systypes the details of each data type and user-defined data type provided by the storage system.

The use of system stored procedures, system functions, information architecture views, and system tables makes it easy for us to get all the metadata information we need.

Example:

1, get the name of all the user tables in the current database.

SELECT object_name (ID)
from sysobjects
WHERE xtype = ' U ' and objectproperty (ID, ' ismsshipped ') = 0

The main use of the system table sysobjects and its properties xtype, as well as the use of OBJECTPROPERTY system functions to determine whether the SQL Server is installed in the process of creating objects.

2. Get all the index names on the specified table.

SELECT name from sysindexes
WHERE id = object_id (' mytable ') and indid > 0

Comprehensive example

The following is a stored procedure that automatically encrypts the user stored procedures of the current database.

DECLARE @sp_name nvarchar
DECLARE @sp_content nvarchar
DECLARE @asbegin int
DECLARE @now DateTime
Select @now = getdate ()
DECLARE sp_cursor cursor for
select object_name (IDs) from
sysobjects
WHERE xtype = ' P '
and type = ' P '
and Crdate < @now
and OBJECTPROPERTY (ID, ' ismsshipped ') =0

OPEN sp_cursor

FETCH NEXT from sp_cursor to
@sp_name while

@ @FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text from
syscomments WHERE id = object_id (@sp_name)
Select @asbegin =
PATINDEX ('%as ' + char + '% ', @sp_content)
Select @sp_content =
SUBSTRING (@sp_conte  NT, 1, @asbegin-1)
+ ' with encryption as '
+ SUBSTRING (@sp_content, @asbegin +2, LEN (@sp_content))
SELECT @sp_name = ' DROP PROCEDURE [' + @sp_name + '] '
exec sp_executesql @sp_name
exec sp_executesql @sp_content
FETCH NEXT from sp_cursor to @sp_name end close

sp_cursor
deallocate sp_cursor

The stored procedure uses the sysobjects and syscomments tables and subtly modifies the SQL definition statements of the original stored procedure to modify the as with encryption as to achieve the purpose of the encryption stored procedure. This stored procedure is passed on SQL Server 2000.