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:
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:
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:
'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:
filegroup_id ('nhiberna') -- output null 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:
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:
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_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:
From Sys.database_files -- Find out which database files are file_id ('NHibernate- -
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:
From Sys.database_files -- Find out what database files are file_id ('NHibernate-- Output 1 Locate the NHibernate database file corresponding to the Fileid file_name (-- 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 (file_name, 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:
SelectFileproperty (‘NHibernate‘,‘IsReadOnly‘)--Output 0SelectFileproperty (‘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:
'[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:
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