Develop Paper
  • Program
  • Server
  • Development Tool
  • Blockchain
  • Database
  • Artificial Intelligence
Position: Home > Blogs > DBT > MsSql > Content

Several methods of obtaining SQL Server database metadata

Time:2021-3-6

Introduction to metadata

The most common definition of metadata is “structural data about data”, or simply “information about data”. Legends, library catalog cards and business cards in daily life can be regarded as metadata. In relational database management system (DBMS), metadata describes the structure and meaning of data. For example, when managing and maintaining SQL server or developing database applications, we often need to obtain some information related to database architecture

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 and description of each column;
Constraints defined on a table;
Index defined on a table and primary / foreign key information.

Next, we will introduce several ways to get metadata.

Acquiring Metadata

Using system stored procedures and system functions to access metadata

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

System stored procedures and system functions provide an abstraction layer between system tables and metadata, so that we can get metadata of current database objects without directly querying system tables.

The commonly used system stored procedures related to metadata are as follows:

System stored procedure

◆sp_ Columns returns the details of the columns of the specified table or view.
◆sp_ Databases returns the basic information of all databases on the current server.
◆sp_ Fkeys: if the parameter is a table with a primary key, all the tables containing the foreign key pointing to the table are returned; if the parameter is a table name with a foreign key, all the tables associated with the foreign key through the primary key / foreign key relationship are returned.
◆sp_ Pkeys returns the primary key information of the specified table.
◆sp_ server_ Info returns various characteristics of the current server and their corresponding values.
◆sp_ sproc_ Columns returns the input and output parameters of the specified stored procedure.
◆sp_ Statistics returns all indexes and statistics on the specified table or indexed view.
◆sp_ stored_ Procedures returns the list of stored procedures in the current database, including system stored procedures.
◆sp_ Tables returns all tables and views of the current database, including system tables.

The commonly used system functions related to metadata are as follows:

System function

Column property returns information about column or procedure parameters, such as whether null value is allowed, whether it is a calculated column, etc.
◆COL_ Length returns the specified attribute value of the specified database, such as whether it is in read-only mode or not.
Database propertyex returns the current settings of the specified options or attributes of the specified database, such as the state of the database and the recovery model.
◆OBJECT_ ID returns the identification number of the specified database object name
◆OBJECT_ Name returns the object name of the specified database object ID.
Objectproperty returns the information about the ID number of the specified database object, such as whether it is a table, whether it is a constraint, etc.
◆fn_ Listening property returns the extended property values of database objects, such as object description, format rule, input mask, etc.

Because we can’t directly use the returned results of stored procedures and functions, we will use system stored procedures and functions to query metadata only when we care about the query results and don’t need to further use these results.

For example, to obtain the basic information of all databases on the current server, we can run it in the query analyzer

EXEC sp_databases
GO

In the returned results, we can see the name, size and remarks of the database.

But if we want to refer to this part of information, or store this part of information for later use, we must use the intermediate 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

Using information architecture view to access metadata

Information architecture view is based on the definition of architecture view in SQL-92 standard. These views are independent of system tables and provide internal views about SQL Server metadata. The biggest advantage of information architecture view is that even if we make important changes to the system table, the application can use these views to access normally. Therefore, for the application, as long as it is in line with the SQL-92 standard database system, the use of information architecture view can always work normally.

Information architecture view

◆INFORMATION_ SCHEMA.CHECK_ Constraints: returns information about column or procedure parameters, such as whether null values are allowed, whether calculated columns are allowed, etc.
◆INFORMATION_ SCHEMA.COLUMNS : returns all the columns and their basic information that the current user can access in the current database.
◆INFORMATION_ SCHEMA.CONSTRAINT_ COLUMN_ Usage: returns all columns in the current database with constraints defined and their constraint names.
◆INFORMATION_ SCHEMA.CONSTRAINT_ TABLE_ Usage: returns all tables in the current database with constraints defined and their constraint names.
◆INFORMATION_ SCHEMA.KEY_ COLUMN_ Usage: returns all columns in the current database that are used as primary / foreign key constraints.
◆INFORMATION_ SCHEMA.SCHEMATA : returns all the databases that the current user has permissions and their basic information.
◆INFORMATION_ SCHEMA.TABLES : returns all tables or views and their basic information in the current database with the permission of the current user.
◆INFORMATION_ SCHEMA.VIEWS : returns the view and its owner, definition and other information that the current user can access in the current database.

Because these information architectures exist in the form of views, we can easily obtain and use the information we need.

For example, to find out how many columns there are in a table, we can use the following statement:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=’mytable’

Using system tables to access metadata

Although the use of system stored procedures, system functions and information architecture view can provide us with quite rich metadata information, we still need to query the system table directly for some special metadata information. Because SQL server stores the information of all database objects in the system table, as the administrator and developer of SQL server, understanding the function of each system table will help us understand the internal working principle of SQL server.

There are many system tables in SQL server, among which the most commonly used tables related to metadata query are as follows:
Syscolumns stores the information of each column in each table and view, as well as the information of each parameter in the stored procedure.
Syscomments stores the original SQL text statements that contain each view, rule, default value, trigger, check constraint, default constraint, and stored procedure.
Sysconstraints stores the basic information of each constraint in the current database.
Sysdatabases stores the basic information of each database on the current server.
Sysindexes stores information about each index in the current database.
Sysobjects stores the basic information of each object (constraints, default values, logs, rules, stored procedures, etc.) in the database.
Sysreferences stores all columns including foreign key constraints.
Systypes stores the details of each data type and user-defined data type provided by the system.

By combining system stored procedure, system function, information architecture view with system table, we can easily get all the required metadata information.

Example:

1. Gets the names of all user tables in the current database.


SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0

The system table sysobjects and its attribute xtype are mainly used, and the objectproperty system function is used to determine whether the object is created in the process of installing SQL server.

2. Gets the names of all indexes on the specified table.

SELECT name FROM sysindexes
WHERE id = OBJECT_ID (‘mytable’) AND indid > 0

Comprehensive examples

The following is a stored procedure, whose function is to automatically encrypt the user stored procedure of the current database.


DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = 'P'
AND type = 'P'
AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped')=0

OPEN sp_cursor

FETCH NEXT FROM sp_cursor
INTO @sp_name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM
syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin =
PATINDEX ( '%AS' + char(13) + '%', @sp_content)
SELECT @sp_content =
SUBSTRING(@sp_content, 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
INTO @sp_name
END

CLOSE sp_cursor
DEALLOCATE sp_cursor

The stored procedure makes use of the sysobjects and syscomments tables, and cleverly modifies the SQL definition statement of the original stored procedure, changing as to with encryption as, so as to achieve the purpose of encrypting the stored procedure. This stored procedure is implemented on SQL Server 2000.

Tags: Database, metadata

Recommended Today

Use of Android WebView (super detailed usage)

1.1 overview of WebView Android WebView is a special view on the Android platform. It can be used to display web pages. This WebView class can be used to display only one online web page in the app. Of course, it can also be used to develop browsers. The internal implementation of WebView uses WebKit […]

  • Cast() function in SQL
  • This paper introduces two methods of backup and restore MySQL data
  • On the special space in SQL Server
  • Introduction of SQL function substr
  • In depth analysis of SQL server stored procedures
  • SQL Server traverses data using table variables or temporary tables
  • Detailed explanation of several questions about SQL (SQL advanced)
  • The use of SQL server window function over() instead of cursor
  • A question about updating database (classical parent-child ID Association)
  • SQL server automatically back up and regularly clean up backup files
Pre: Solve the error problem of loading the trained model by python
Next: The method of referencing static library. A file in go development

    Tags

    address algorithm android array assembly attribute Browser c Catalog Character string Client code command configuration file css data Database data base Edition element Example file function html html5 ios java javascript linux Memory method mysql node object page parameter php Plug-in unit project python Route source code The server Thread user

    Recent Posts

    • Use of Android WebView (super detailed usage)
    • Prefix infix suffix expression rules
    • Talking about Python: consolidating the foundation of Python
    • How to realize it through Python — monitoring whether someone remotely logs on the server (detailed thinking + code)
    • Sass environment / grammar / mobile priority project practice

    Recent Comments

    • 7nz on Answer for Click the navigation bar of the web page on the IOS mobile phone and there is no response
    • undefind_5 on Answer for Click the navigation bar of the web page on the IOS mobile phone and there is no response
    • 7nz on Answer for Click the navigation bar of the web page on the IOS mobile phone and there is no response
    • imango on Answer for Can you modify the transaction propagation property of a method @ transactional in spring after propagation is set?
    • Shrem of the Jura forest on Answer for Can you modify the transaction propagation property of a method @ transactional in spring after propagation is set?

    Categories

    • .NET Core
    • Agile Development
    • Android
    • Apple MAC
    • Architecture Design
    • Artificial Intelligence
    • ASP.NET
    • Blockchain
    • C
    • C#
    • C++
    • Database
    • Development Tool
    • Embedded
    • Erlang
    • Freshman
    • Golang
    • HTML/CSS
    • HTML5
    • Information Security
    • IOS
    • Java
    • JavaScript
    • JSP
    • Linux
    • MongoDB
    • MsSql
    • MySql
    • OOP
    • oracle
    • Other DB
    • Other Technology
    • Perl
    • PHP
    • Program
    • Python
    • Redis
    • Regular Expression
    • Ruby
    • Rust
    • SAP
    • Server
    • VBS
    • VUE
    • WEB Front End
    • Windows
    • XML/XSLT
  • java
  • php
  • python
  • linux
  • windows
  • android
  • ios
  • mysql
  • html
  • .net
  • github
  • node.js

Copyright © 2021 Develop Paper All Rights Reserved   

  Sitemap    About DevelopPaper    Privacy Policy    Contact Us