SQL Server Common metadata functions

meta-data functions

　　1. Get the database identifier DB_ID

The DB_ID function is used to get the unique ID (int data type) of the current database, and the database ID is used to uniquely differentiate the library on the server.

Syntax structure:

db_id ([' database_name '])

The database_name in the parameter is the database name of the sysname type, which is an optional parameter. Returns the ID of the current working database if it is not specified.

return value:

The database identifier for the INT data type.

Example:

Select db_id ()    -- output 8

　　2. Get the database name Db_name

The Db_name function is used to red zone the name of the current database (nvarchar data type)

Syntax structure:

db_name ([' database_id '])

The database in the parameter is an int data type that is an optional parameter. Returns the name of the current working database if it is not specified.

Example:

Select db_name ()    -- output NHibernate

　　3. Get Database Properties Databasepropertyex

The DATABASEPROPERTYEX function is used to obtain the current settings for a property of a specified database. Once the execution returns a property value, you can query the Sys.database system view of the Master System database to return multiple property values.

Syntax structure:

DATABASEPROPERTYEX (database, property)

Parameter description:

Database: The nvarchar (128) data type that represents the name of the information for which to return the named property

Property: Represents the database attribute to be returned, the data type is varchar (128)

Example:

Select databasepropertyex ('NHibernate','recovery' )    -- Output Simple This is the recovery model of the database

The value and description of the property attribute are as follows:

Property value	Description	return value
Collation	Sort Rule Name	nvarchar (128), NULL
IsAutoClose	Whether the automatic shutdown feature of the database is enabled	INT, NULL
Isautocreatestatistics	Whether statistical information is automatically created	INT, NULL
Isautoshrink	Whether to shrink regularly	INT, NULL
IsAutoUpdateStatistics	is automatically able to update statistics	INT, NULL
Recovery	Recovery model of the database	nvarchar (128)
Status	Status of the database	nvarchar (128)
Updateability	Whether the data can be modified	nvarchar (128)
UserAccess	Which users can access the database	nvarchar (128)
Version	Database build Number	Int

　　4. Get the filegroup identifier FILEGROUP_ID

filegropy_id function to get a unique identifier for a filegroup (int data type)

Syntax structure:

filegroup_id ' Filegropy_name ' )

The filegropy_name in the parameter is the database name of the sysname type, which can be queried from the sys.filegroups system view.

return value:

The file group identifier for the INT data type.

Example:

　　　　Select filegroup_id ('nhiberna')    -- output NULL Select filegroup_id ('PRIMARY')    -- Output 1　　　　

5. Get file group name Filegroup_name

The Filegroup_name function is used to get the name of the filegroup (nvarchar data type).

Syntax structure:

filegroup_name (filegroup_id)

The filegroup_id in the parameter is the filegroup identifier of the int data type, and to obtain the identifier, you can query the Sys.filegroups system view

return value:

nvarchar (128) file group name for data type

Example:

Select filegroup_name (1)    -- Output PRIMARY

　　6. Get Filegroup attribute value Filegroupproperty

The Filegroupproperty function is used to obtain the specified attribute value for the specified filegroup.

Syntax structure:

Filegroupproperty (Filegroupname,property)

Parameter description:

Filegroup_name: Represents the filegroup name for which to return property information, the nvarchar (128) data type, which can be obtained through the sys.filegroups column.

Property: Represents the attribute to be returned with a data type of varchar (128).

List of common property attribute values

Property value	Description	return value
IsReadOnly	Whether the filegroup is read-only	Int, Null
Isuserdefinedeg	is a user-defined filegroup	Int, Null
IsDefault	Whether the default filegroup	Int, Null

Example:

Select Filegroupproperty ('PRIMARY','isreadonly')    -- Output 0

　　7. Get the file identifier file_id

The FILE_ID function is used to get the database files, including the log files and the identifiers of the data files. The file identifiers are used to uniquely differentiate in the database.

Syntax structure:

file_id file_name )

The file_name in the parameter is the file name of the sysname type, which can be obtained by querying the Name column in the System view sys.database_files.

return value:

The file identifier for the smallint data type.

Example:

　　　　Select *  from Sys.database_files    -- Find out which database files are in selectfile_id(' NHibernate ')    -- Output 1 Isolated nhibernate database file corresponding to Fileid    　　　　

　　8. Get the file name file_name

The file_name function is used to get the logical name of the current database (the nvarchar data type), which is the logical name instead of the physical name of the containing path.

Syntax structure:

　　file_name (file_id)

The file_id in the parameter is the database identifier of the INT data type. You can query the file_id column of the sys.master_files system view of the master database for the file ID of all databases on the server, or you can query the Sys.database_files system view of a database File_ ID column gets the ID of the file for the database.

return value:

nvarchar (128) The logical file name of the data type.

Example:

　　　　Select *  from Sys.database_files    -- Find out which database files are in selectfile_id(' NHibernate ')    -- Output 1 Isolated nhibernate database file corresponding to Fileid Select  file_name (1)        -- Output NHibernate　　　　　　　　

　　9. Get the file attribute value Fileproperty

The Fileproperty function is used to obtain the specified property value for the specified file.

Syntax structure:

Fileproperty (, property)

file_name: The logical name of the file that represents the property information to return, the nvarchar (128) data type, which can be obtained by querying the name column of the sys.database_files system view.

Property: Represents the attribute to be returned with a data type of varchar (128).

The values for the common property properties are as follows:

Property value	Description	return value
IsReadOnly	Whether the file is read-only	Int 1 for true,0 rep false
Isprimaryfile	is the primary data file	Int 1 for true,0 rep false
Islogfile	is the log file	Int 1 for true,0 rep false
SpaceUsed	Amount of file space used	Int

Example:

 　　　　 Select Fileproperty('NHibernate','isreadonly')--Output 0 　　　　 Select Fileproperty('NHibernate','Isprimaryfile')--Output 1 　　　　 Select Fileproperty('NHibernate','Islogfile')--Output 0 　　　　 Select Fileproperty('NHibernate','spaceused')--Output 184

　　10. Get the Database object identifier object_id

The OBJECT_ID function is used to obtain a unique ID (int data type) for a particular database, a specific data object under a specific schema, and an object's ID is used to differentiate the unique within the database.

Syntax structure:

　　　　　　object_id ' [database_name. [Schema_name]. | Schema_name. ]object_name'[, ' object_type ']')　　　　　　

Parameter description:

database_name	Database name
Schema_name	Schema name
object_name	Object Name
Object_type	Object type

return value:

An object identifier for the int data type.

　　11. Get the Database object name object_name

The object_name function is used to get the logical name of the data object under the specified database, specified schema. The name can be obtained by querying the name column of the sys.objects system view.

Syntax structure:

　　object_name (object_id)

The object_id in the parameter is an object identifier of the INT data type

return value:

The logical object name of the sysname data type.

Example:

Select object_name (object_id('dbo. account')    -- output Account

　　12. Get the file attribute value ObjectProperty

The OBJECTPROPERTY function is used to obtain the value of the specified property of a specified database, specified object under a given schema.

Syntax structure:

    ObjectProperty (Id,property)

Parameter description:

ID: Represents the Id,int data type of the object for which you want to return named property information.

Property: Represents the attribute to be returned with a data type of sql_variant, which is a variant.

Common Property attribute values

Property value	Description	return value
Tablehastextimage	Whether the table contains text, image columns	Int 1 represents true 0 for false
Tablehasprimarykey	Whether the table contains a primary key	Int 1 represents true 0 for false
Tablehasindex	Whether the table contains an index	Int 1 represents true 0 for false
Tablehasforeignkey	Whether the table contains foreign keys	Int 1 represents true 0 for false
SchemaID	The schema ID of the object	Int
ownerID	Owner of the object	Int
Isview	Whether the view	Int 1 represents true 0 for false
Isusertable	Whether the user creates the table	Int 1 represents true 0 for false
Istable	Whether it is a table	Int 1 represents true 0 for false
Issystemtable	Whether the system table	Int 1 represents true 0 for false
IsPrimaryKey	Whether the primary key	Int 1 represents true 0 for false

SQL Server Common metadata functions