SQL內置Function--元数据函数
这些标量函数返回有关数据库和数据库对象的信息。
|
COL_LENGTH |
fn_listextendedproperty |
|
COL_NAME |
FULLTEXTCATALOGPROPERTY |
|
COLUMNPROPERTY |
FULLTEXTSERVICEPROPERTY |
|
DATABASEPROPERTY |
INDEX_COL |
|
DATABASEPROPERTYEX |
INDEXKEY_PROPERTY |
|
DB_ID |
INDEXPROPERTY |
|
DB_NAME |
OBJECT_ID |
|
FILE_ID |
OBJECT_NAME |
|
FILE_NAME |
OBJECTPROPERTY |
|
FILEGROUP_ID |
@@PROCID |
|
FILEGROUP_NAME |
SQL_VARIANT_PROPERTY |
|
FILEGROUPPROPERTY |
TYPEPROPERTY |
|
FILEPROPERTY |
|
所有元数据函数都具有不确定性。每次用一组特定的输入值调用它们时，所返回的结果不总是相同。有关函数确定性的更多信息，请参见确定性函数和非确定性函数。
1COL_LENGTH
返回列的定义长度（以字节为单位）。
语法
COL_LENGTH ( 'table' , 'column' )
参数
'table'
为其确定列长度信息的表的名称。table 是一个 nvarchar 类型的表达式。
column
为其确定长度的列的名称。column 是一个 nvarchar 类型的表达式。
返回类型
int
示例
下例为 varchar(40) 类型的列和 nvarchar(40) 类型的列显示返回值。
USE pubs
GO
CREATE TABLE t1
(c1 varchar(40),
c2 nvarchar(40)
)
GO
SELECT COL_LENGTH('t1','c1')AS 'VarChar',
COL_LENGTH('t1','c2')AS 'NVarChar'
GO
DROP TABLE t1
下面是结果集：
VarChar NVarChar
40 80
2COL_NAME
返回数据库列的名称，该列具有相应的表标识号和列标识号。
语法
COL_NAME ( table_id , column_id )
参数
table_id
包含数据库列的表的标识号。table_id 属于 int 类型。
column_id
列的标识号。column_id 参数属于 int 类型。
返回类型
sysname
注释
table_id 和 column_id 参数共同产生一个列名称字符串。
有关获得表和列标识号的更多信息，请参见 OBJECT_ID。
示例
下面的示例返回 Northwind 数据库的 Employees 表中的首列的名称。
USE Northwind
SET NOCOUNT OFF
SELECT COL_NAME(OBJECT_ID('Employees'), 1)
下面是结果集：
EmployeeID
(1 row(s) affected)
3COLUMNPROPERTY
返回有关列或过程参数的信息。
语法
COLUMNPROPERTY ( id , column , property )
参数
id
一个表达式，其中包含表或过程标识符 (ID)。
column
一个表达式，其中包含列或参数名称。
property
一个表达式，其中包含要为 id 返回的信息。该表达式可以取下列这些值中的任意一个。
|
值 |
描述 |
返回的值 |
|
AllowsNull |
允许空值。 |
1 = TRUE |
|
IsComputed |
该列为计算列。 |
1 = TRUE |
|
IsCursorType |
过程参数属于CURSOR 类型。 |
1 = TRUE |
|
IsDeterministic |
该列具有确定性。该属性只适用于计算列和视图列。 |
1 = TRUE |
|
IsFulltextIndexed |
该列已经注册为全文索引。 |
1 = TRUE |
|
IsIdentity |
该列使用IDENTITY 属性。 |
1 = TRUE |
|
IsIdNotForRepl |
该列检查IDENTITY_INSERT 设置。如果指定了IDENTITY NOT FOR REPLICATION，则不检查IDENTITY_INSERT 设置。 |
1 = TRUE |
|
IsIndexable |
该列可进行索引。 |
1 = TRUE |
|
IsOutParam |
该过程参数为输出参数。 |
1 = TRUE |
|
IsPrecise |
该列是精确的。该属性只适用于具有确定性的列。 |
1 = TRUE |
|
IsRowGuidCol |
该列具有 uniqueidentifier 数据类型并且使用ROWGUIDCOL 属性进行定义。 |
1 = TRUE |
|
Precision |
列或参数的数据类型的精度。 |
指定的列数据类型的精度 NULL = 无效的输入 |
|
Scale |
列或参数的数据类型的小数位数。 |
小数位数 NULL = 无效的输入 |
|
UsesAnsiTrim |
当开始创建表时，ANSI 填充设置为 ON。 |
1 = TRUE |
返回类型
int
注释
当检查列的确定性属性时，首先测试该列是否为计算列。IsDeterministic 对于非计算列返回 NULL。
可以将计算列指定为索引列。
示例
下面的示例返回 au_lname 列的长度。
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','PRECISION')
4DATABASEPROPERTY
返回给定数据库和属性名的命名数据库属性值。
重要 使用 Microsoft® SQL Server™ 2000 函数 DATABASEPROPERTYEX 获得有关数据库选项的当前设置或指定数据库属性的信息。提供 DATABASEPROPERTY 函数是为了向后兼容。
语法
DATABASEPROPERTY( database , property )
参数
database
是包含有数据库名的表达式，返回此数据库的命名属性信息。database 是 nvarchar(128)。
property
是一个表达式，包含将要返回的数据库属性的名称。property 的数据类型为 varchar(128)，可以是下列值中的一个。
|
Value |
描述 |
返回的值 |
|
IsAnsiNullDefault |
数据库遵循 SQL-92 规则，允许空值。 |
1 = TRUE |
|
IsAnsiNullsEnabled |
所有与空值的比较取值为未知。 |
1 = TRUE |
|
IsAnsiWarningsEnabled |
当标准错误条件发生时，发出错误或警告消息。 |
1 = TRUE |
|
IsAutoClose |
数据库在最后一位用户退出后完全关闭并释放资源。 |
1 = TRUE |
|
IsAutoCreateStatistics |
当因表中数据改变造成统计过期时，自动更新当前统计。 |
1 = TRUE |
|
IsAutoShrink |
数据库文件可以自动定期收缩。 |
1 = TRUE |
|
IsAutoUpdateStatistics |
启用自动更新统计数据库选项。 |
1 = TRUE |
|
IsBulkCopy |
数据库允许无日志记录的操作。 |
1 = TRUE |
|
IsCloseCursorsOnCommitEnabled |
关闭在提交事务时打开的游标。 |
1 = TRUE |
|
IsDboOnly |
数据库处于仅 DBO 访问模式。 |
1 = TRUE |
|
IsDetached |
分离操作分离了数据库。 |
1 = TRUE |
|
IsEmergencyMode |
启用紧急模式，允许使用可疑数据库。 |
1 = TRUE |
|
IsFulltextEnabled |
数据库是全文启用。 |
1 = TRUE |
|
IsInLoad |
正在装载数据库。 |
1 = TRUE |
|
IsInRecovery |
正在恢复数据库。 |
1 = TRUE |
|
IsInStandBy |
数据库以只读方式联机，并允许还原日志。 |
1 = TRUE |
|
IsLocalCursorsDefault |
游标声明默认为 LOCAL。 |
1 = TRUE |
|
IsNotRecovered |
数据库不能恢复。 |
1 = TRUE |
|
IsNullConcat |
空串联操作数产生 NULL。 |
1 = TRUE |
|
IsOffline |
数据库脱机。 |
1 = TRUE |
|
IsQuotedIdentifiersEnabled |
双引号可用于标识符。 |
1 = TRUE |
|
IsReadOnly |
数据库处于只读访问模式。 |
1 = TRUE |
|
IsRecursiveTriggersEnabled |
启用触发器循环启动。 |
1 = TRUE |
|
IsShutDown |
数据库启动时遇到问题。 |
1 = TRUE |
|
IsSingleUser |
数据库处于单用户访问模式。 |
1 = TRUE |
|
IsSuspect |
数据库可疑。 |
1 = TRUE |
|
IsTruncLog |
数据库截断其登录检查点。 |
1 = TRUE |
|
Version |
创建数据库时所使用的 Microsoft® SQL Server™ 代码的内部版本号。仅供 SQL Server 工具在内部用于升级处理。 |
Version number = 数据库打开 |
1. 如果从未启动数据库，或者已经自动关闭数据库，则返回值也为 NULL。
返回类型
integer
示例
此示例返回 master 数据库 IsTruncLog 属性的设置。
USE master
SELECT DATABASEPROPERTY('master', 'IsTruncLog')
下面是结果集：
-------------------
1
5DATABASEPROPERTYEX
返回指定数据库的指定数据库选项或属性的当前设置。
语法
DATABASEPROPERTYEX( database , property )
参数
database
是取值为数据库名的表达式，将返回此数据库的属性设置。database 是 nvarchar(128)。
property
是表明应返回的选项或属性设置的表达式。property 是 nvarchar(128)，并可以是下列值之一。
|
值 |
描述 |
返回的值 |
|
Collation |
数据库默认排序规则名。 |
排序规则名 |
|
IsAnsiNullDefault |
数据库遵循 SQL-92 规则，允许空值。 |
1 = TRUE |
|
IsAnsiNullsEnabled |
所有与空值的比较取值为未知。 |
1 = TRUE |
|
IsAnsiPaddingEnabled |
在比较或插入前，填充字符串至相同长度。 |
1 = TRUE |
|
IsAnsiWarningsEnabled |
当标准错误条件发生时，发出错误或警告消息。 |
1 = TRUE |
|
IsArithmeticAbortEnabled |
当在查询执行中发生溢出或被零除的错误时终止查询。 |
1 = TRUE |
|
IsAutoClose |
数据库在最后一位用户退出后完全关闭并释放资源。 |
1 = TRUE |
|
IsAutoCreateStatistics |
当因表中数据改变造成统计过期时，自动更新当前统计。 |
1 = TRUE |
|
IsAutoShrink |
数据库文件可以自动定期收缩。 |
1 = TRUE |
|
IsAutoUpdateStatistics |
启用自动更新统计数据库选项。 |
1 = TRUE |
|
IsCloseCursorsOnCommitEnabled |
关闭在提交事务时打开的游标。 |
1 = TRUE |
|
IsFulltextEnabled |
数据库是全文启用。 |
1 = TRUE |
|
IsInStandBy |
数据库以只读方式联机，并允许还原日志。 |
1 = TRUE |
|
IsLocalCursorsDefault |
游标声明默认为 LOCAL。 |
1 = TRUE |
|
IsMergePublished |
如果安装了复制，可以发布数据库表供复制。 |
1 = TRUE |
|
IsNullConcat |
空串联操作数产生 NULL。 |
1 = TRUE |
|
IsNumericRoundAbortEnabled |
当表达式中没有精度时产生错误。 |
1 = TRUE |
|
IsQuotedIdentifiersEnabled |
双引号可用于标识符。 |
1 = TRUE |
|
IsRecursiveTriggersEnabled |
启用触发器循环启动。 |
1 = TRUE |
|
IsSubscribed |
可以订阅数据库来发布。 |
1 = TRUE |
|
IsTornPageDetectionEnabled |
Microsoft® SQL Server™ 检测因电力故障或其它系统故障造成的不完全 I/O 操作。 |
1 = TRUE |
|
Recovery |
数据库的恢复模型。 |
FULL = 完全恢复模型 |
|
SQLSortOrder |
SQL Server 早期版本支持的 SQL Server 排序次序 ID。 |
0 = 数据库正使用 Windows 排序规则 |
|
Status |
数据库状态。 |
ONLINE = 数据库可供查询 |
|
Updateability |
表明是否可以修改数据。 |
READ_ONLY = 数据可读，但不可修改 |
|
UserAccess |
表明哪个用户可以访问数据库。 |
SINGLE_USER = 每次仅一个 db_owner, dbcreator, 或 sysadmin 用户 |
|
Version |
创建数据库时使用的 Microsoft SQL Server 代码内部版本号。仅供 SQL Server 工具在内部用于升级处理。 |
Version number = 数据库打开 |
返回类型
sql_variant
注释
此函数一次只返回一个属性设置。
DATABASEPROPERTY 支持向后兼容性，但不提供此版本中添加的属性信息。另外，DATABASEPROPERTYEX中新的属性已经替换了 DATABASEPROPERTY 支持的许多属性。
示例
A. 检索 autoshrink 数据库选项的状态
此示例返回 Northwind 数据库的 autoshrink 数据库选项的状态。
SELECT DATABASEPROPERTYEX('Northwind', 'IsAutoShrink')
下面是结果集（表明 autoshrink 关闭）：
------------------
0
B. 检索数据库默认排序规则
此示例返回 Northwind 数据库默认排序规则名。
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')
下面是结果集：
------------------------------
SQL_Latin1_General_CP1_CS_AS
6DB_ID
返回数据库标识 (ID) 号。
语法
DB_ID ( [ 'database_name' ] )
参数
'database_name'
是用来返回相应数据库 ID 的数据库名。database_name 是 nvarchar。如果不填 database_name，则返回当前数据库 ID。
返回类型
smallint
示例
此示例检查在 sysdatabases 中的每个数据库，使用数据库名来确定数据库 ID。
USE master
SELECT name, DB_ID(name) AS DB_ID
FROM sysdatabases
ORDER BY dbid
下面是结果集：
name DB_ID
------------------------------ ------
master 1
tempdb 2
model 3
msdb 4
pubs 5
(5 row(s) affected)
7DB_NAME
返回数据库名。
语法
DB_NAME ( database_id )
参数
database_id
是应返回数据库的标识号 (ID)。database_id 是 smallint，没有默认值。如果没有指定 ID，则返回当前数据库名。
返回类型
nvarchar(128)
示例
此示例检查 sysdatabases 中的每个数据库，使用数据库标识号来确定数据库名。
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
ORDER BY dbid
GO
下面是结果集：
dbid DB_NAME
------ ------------------------------
1 master
2 tempdb
3 model
4 msdb
5 pubs
(5 row(s) affected)
8FILE_ID
返回当前数据库中给定逻辑文件名的文件标识 (ID) 号。
语法
FILE_ID ( 'file_name' )
参数
'file_name'
要返回其文件 ID 的文件的名称。file_name 为 nchar(128)。
返回类型
smallint
注释
file_name 对应于 sysfiles 中的 name 列。
示例
下例返回 master 数据库的文件 ID (1)。
USE master
SELECT FILE_ID('master')
9FILE_NAME
返回给定文件标识 (ID) 号的逻辑文件名。
语法
FILE_NAME ( file_id )
参数
file_id
要返回其对应文件名的文件标识号。file_id 为 smallint。
返回类型
nvarchar(128)
注释
file_ID 对应于 sysfiles 中的 fileid 列。
示例
下例返回 file_ID 为 1 的文件名（master 数据库文件）。
USE master
SELECT FILE_NAME(1)
--
10FILEGROUP_ID
返回给定文件组名称的文件组标识 (ID) 号。
语法
FILEGROUP_ID ( 'filegroup_name' )
参数
'filegroup_name'
要返回其文件组 ID 的文件组名。filegroup_name 为 nvarchar(128)。
返回类型
smallint
注释
filegroup_name 对应于 sysfilegroups 中的 groupname 列。
示例
下例返回名为 default 的文件组的文件组 ID。
USE master
SELECT FILEGROUP_ID('default')
11FILEGROUP_NAME
返回给定文件组标识 (ID) 号的文件组名。
语法
FILEGROUP_NAME ( filegroup_id )
参数
filegroup_id
为其返回文件组名的文件组 ID 号。filegroup_id 为 smallint。
返回类型
nvarchar(128)
注释
filegroup_id 对应于 sysfilegroups 中的 groupid 列。
示例
下例返回文件组 ID 为 1（默认值）的文件组名。
USE master
SELECT FILEGROUP_NAME(1)
12FILEGROUPPROPERTY
给定文件组和属性名时，返回指定的文件组属性值。
语法
FILEGROUPPROPERTY ( filegroup_name , property )
参数
filegroup_name
包含文件组名称的表达式，将为该文件组返回指定的属性信息。filegroup_name 为 nvarchar(128)。
property
包含要返回的文件组属性名称的表达式。property 为 varchar(128)，并且可为以下值之一。
|
Value |
描述 |
返回的值 |
|
IsReadOnly |
文件组名为只读。 |
1 = True |
|
IsUserDefinedFG |
文件组名为用户定义的文件组。 |
1 = True |
|
IsDefault |
文件组名为默认的文件组。 |
1 = True |
返回类型
int
示例
此示例返回主文件组的 IsUserDefinedFG 属性设置。
USE master
SELECT FILEGROUPPROPERTY('primary', 'IsUserDefinedFG')
13FILEPROPERTY
给定文件名和属性名时，返回指定的文件名属性值。
语法
FILEPROPERTY ( file_name , property )
参数
file_name
含有与当前数据库关联的文件名的表达式，将为该数据库返回属性信息。file_name 为 nchar(128)。
property
含有要返回的文件属性名称的表达式。property 为 varchar(128)，并且可为以下值之一。
|
Value |
描述 |
返回的值 |
|
IsReadOnly |
文件为只读。 |
1 = True |
|
IsPrimaryFile |
文件为主文件。 |
1 = True |
|
IsLogFile |
文件为日志文件。 |
1 = True |
|
SpaceUsed |
指定的文件所占用的空间数目。 |
文件中所分配的页数 |
返回类型
int
示例
此示例返回 master 数据库主文件名的 IsPrimaryFile 属性设置。
USE master
SELECT FILEPROPERTY('master', 'IsPrimaryFile')
14fn_listextendedproperty
返回数据库对象的扩展属性值。
语法
fn_listextendedproperty (
{ default | [ @name = ] 'property_name' | NULL }
, { default | [ @level0type = ] 'level0_object_type' | NULL }
, { default | [ @level0name = ] 'level0_object_name' | NULL }
, { default | [ @level1type = ] 'level1_object_type' | NULL }
, { default | [ @level1name = ] 'level1_object_name' | NULL }
, { default | [ @level2type = ] 'level2_object_type' | NULL }
, { default | [ @level2name = ] 'level2_object_name' | NULL }
)
参数
{default|[@name =] 'property_name'|NULL}
是属性的名称。property_name 为 sysname 类型。有效的输入为 default、NULL 或属性名。
{default|[@level0type =] 'level0_object_type'|NULL}
用户或用户定义类型。level0_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入是 USER、TYPE、default 和 NULL。
{default|[@level0name =] 'level0_object_name'|NULL}
指定的 0 级对象类型的名称。level0_object_name 的数据类型为 sysname，其默认值为 NULL。有效的输入为 default、NULL 或对象名。
{default|[@level1type =] 'level1_object_type'|NULL}
1 级对象的类型。level1_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入为 TABLE、VIEW、PROCEDURE、FUNCTION、DEFAULT、RULE、default 和 NULL。
说明 默认映射 NULL，而"default"映射对象类型 DEFAULT。
{default|[@level1name =] 'level1_object_name'|NULL}
指定的 1 级对象类型的名称。level1_object_name 的数据类型为 sysname，其默认值为 NULL。有效的输入为 default、NULL 或对象名。
{default|[@level2type =] 'level2_object_type'|NULL}
2 级对象的类型。level2_object_type 的数据类型为 varchar(128)，其默认值为 NULL。有效的输入为 COLUMN、PARAMETER、INDEX、CONSTRAINT、TRIGGER、DEFAULT、default（映射 NULL）和 NULL。
{default|[@level2name =] 'level2_object_name'|NULL}
指定的 2 级对象类型的名称。level2_object_name 的数据类型为 sysname，其默认值为 NULL。有效的输入为 default、NULL 或对象名。
返回表
下面是 fn_listextendedproperty 返回的表的格式。
|
列名 |
数据类型 |
|
objtype |
sysname |
|
objname |
sysname |
|
name |
sysname |
|
value |
sql_variant |
如果返回的表为空，可能对象没有扩展属性或用户没有列出对象扩展属性的权限。
注释
系统对象不允许有扩展属性。
如果 property_name 的值为 NULL 或 default，fn_listextendedproperty 返回对象的所有属性。
如果指定了对象类型且相应的对象名的值为 NULL 或 default，则 fn_listextendedproperty 返回指定类型的所有对象的所有扩展属性。
对象是按级别区分的，0 级为最高，2 级为最低。如果指定了较低级别的对象（1 或 2 级）类型和名称，则父对象类型和名称的值不能为 NULL 或 default。否则，函数将返回错误。
用于列出某些级别对象类型的扩展属性的权限有所不同。
- 对于级别 0 的对象，如果某用户为级别 0 的名称中所标识的用户，或者是 db_owner 和 db_ddladmin 固定数据库角色的成员，则该用户可以列出指定"user"类型的扩展属性。
- 所有用户可使用 0 级对象类型"type"列出扩展属性。
- 对于 1 级对象，如果用户为对象的所有者或用户对于对象有权限，该用户可以用任何有效的类型值列出扩展属性。
- 对于 2 级对象，如果当前用户对父对象（1 级和 0级）有任何权限，该用户可以用任何有效的类型值列出扩展属性。
示例
此示例列出数据库的所有扩展属性。
SELECT *
FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)
-或-
SELECT *
FROM ::fn_listextendedproperty(default, default, default, default, default, default, default)
此示例列出表"T1"中所有列的所有扩展属性。
CREATE table T1 (id int , name char (20))
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)
下面是结果集：
|
objtype |
objname |
name |
value |
|
COLUMN |
id |
caption |
Employee ID |
|
COLUMN |
name |
caption |
Employee Name |
15FULLTEXTCATALOGPROPERTY
返回有关全文目录属性的信息。
语法
FULLTEXTCATALOGPROPERTY ( catalog_name , property )
参数
catalog_name
包含全文目录名称的表达式。
property
包含全文目录属性名称的表达式。下表列出了这些属性，并提供所返回信息的描述。
|
属性 |
描述 |
|
PopulateStatus |
0 = 空闲 |
|
ItemCount |
全文目录中当前全文索引项数目。 |
|
IndexSize |
全文索引的大小 (MB)。 |
|
UniqueKeyCount |
在此目录中组成全文索引的唯一字（键）的数目。这是全文目录中所存储的无干扰字数目的近似值。 |
|
LogSize |
与 Microsoft® 搜索服务全文目录相关联的错误日志组合集的大小，以字节表示。 |
|
PopulateCompletionAge |
上一次全文索引填充的完成时间与 01/01/1990 00:00:00 之间的时间差，用秒表示。 |
返回类型
int
注释
不应使应用程序在紧密循环中等待，该循环不断检查 PopulateStatus 属性是否变为空闲（表明填充已完成），这一点很重要，因为如果等待，会使 CPU 游离于数据库和全文检索处理之外，从而导致超时。
示例
此示例返回 Cat_Desc 全文目录中的全文索引项数目。
USE Northwind
GO
SELECT fulltextcatalogproperty('Cat_Desc', 'ItemCount')
下面是结果集：
-----------
9
16FULLTEXTSERVICEPROPERTY
返回有关全文服务级别属性的信息。
语法
FULLTEXTSERVICEPROPERTY ( property )
参数
property
含有全文服务级别属性名称的表达式下表列出了这些属性，并提供所返回信息的描述。
|
属性 |
值 |
|
ResourceUsage |
一个从 1（后台）到 5（专用）之间的值。 |
|
ConnectTimeout |
在超时发生前，Microsoft 搜索服务等待所有与 Microsoft® SQL Sever™ 数据库服务器的连接完成以便进行全文索引填充所用的时间（以秒为单位）。 |
|
IsFulltextInstalled |
在 SQL Server 的当前实例中安装全文组件。 1 = 已安装全文组件。 |
|
DataTimeout |
在超时发生前，Microsoft 搜索服务等待所有由 Microsoft SQL Server 数据库服务器返回数据以便进行全文索引填充所用的时间（以秒为单位）。 |
返回类型
int
示例
下例验证 ® 搜索服务是否已经安装。
SELECT fulltextserviceproperty('IsFulltextInstalled')
下面是结果集：
-----------
1
17INDEX_COL
返回索引列名称。
语法
INDEX_COL ( 'table' , index_id , key_id )
参数
'table'
表的名称。
index_id
索引的 ID。
key_id
键的 ID。
返回类型
nvarchar (256)
示例
下面的示例将产生 authors 表中索引的列表。
USE pubs
-- Declare variables to use in this example.
DECLARE @id int, @type char(2),@msg varchar(80),
@indid smallint, @indname sysname, @status int,
@indkey int, @name varchar(30)
-- Obtain the identification number for the authors table to look up
-- its indexes in the sysindexes table.
SET NOCOUNT ON
SELECT @id = id, @type = type
FROM sysobjects
WHERE name = 'authors' and type = 'U'
-- Start printing the output information.
print 'Index information for the authors table'
print '---------------------------------------'
-- Loop through all indexes in the authors table.
-- Declare a cursor.
DECLARE i cursor
FOR
SELECT indid, name, status
FROM sysindexes
WHERE id = @id
-- Open the cursor and fetch next set of index information.
OPEN i
FETCH NEXT FROM i INTO @indid, @indname, @status
IF @@FETCH_STATUS = 0
PRINT ' '
-- While there are still rows to retrieve from the cursor,
-- find out index information and print it.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg = NULL
-- Print the index name and the index number.
SET @msg = ' Index number ' + CONVERT(varchar, @indid)+
' is '+@indname
SET @indkey = 1
-- @indkey (equivalent to key_id in the syntax diagram of
-- INDEX_COL) can be from 1 to 16.
WHILE @indkey <= 16 and INDEX_COL(@name, @indid, @indkey)
IS NOT NULL
BEGIN
-- Print different information if @indkey <> 1.
IF @indkey = 1
SET @msg = @msg + ' on '
+ index_col(@name, @indid, @indkey)
ELSE
SET @msg = @msg + ', '
+ index_col(@name, @indid, @indkey)
SET @indkey = @indkey + 1
END
PRINT @msg
SET @msg = NULL
FETCH NEXT FROM i INTO @indid, @indname, @status
END
CLOSE i
DEALLOCATE i
SET NOCOUNT OFF
下面是结果集：
Index information for the authors table
---------------------------------------
Index number 1 is UPKCL_auidind
Index number 2 is aunmind
18INDEXKEY_PROPERTY
返回有关索引键的信息。
语法
INDEXKEY_PROPERTY ( table_ID , index_ID , key_ID , property )
参数
table_ID
表标识号。table_ID 的数据类型为 int。
index_ID
索引标识号。index_ID 的数据类型为 int。
key_ID
索引列的位置。key_ID 的数据类型为 int。
property
属性的名称，将要为该属性返回信息。property 是字符串，可以是下面值中的一个。
|
Value |
描述 |
|
ColumnId |
索引的 key_ID 位置上的列 ID。 |
|
IsDescending |
存储索引列的顺序。 1 = 降序 |
返回类型
int
示例
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'ColumnId')
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDescending')
19INDEXPROPERTY
在给定表标识号、索引名称及属性名称的前提下，返回指定的索引属性值。
语法
INDEXPROPERTY ( table_ID , index , property )
参数
table_ID
是包含要为其提供索引属性信息的表或索引视图标识号的表达式。table_ID 的数据类型为 int。
index
一个包含索引的名称的表达式，将为该索引返回属性信息。index 的数据类型为 nvarchar(128)。
property
一个表达式，它包含将要返回的数据库属性的名称。property 的数据类型为 varchar(128)，可以是下列值中的一个。
|
属性 |
描述 |
|
IndexDepth |
索引的深度。 返回索引所具有的级别数。 |
|
IndexFillFactor |
索引指定自己的填充因子。 返回创建索引或最后重建索引时使用的填充因子。 |
|
IndexID |
指定表或索引视图上的索引的索引 ID。 |
|
IsAutoStatistics |
索引是由 sp_dboption 的 auto create statistics 选项生成的。 1 = True |
|
IsClustered |
索引是聚集的。 1 = True |
|
IsFulltextKey |
索引是表的全文键。 1 = True |
|
IsHypothetical |
索引是假设的，不能直接用作数据访问路径。假设的索引保留列级统计。 1 = True |
|
IsPadIndex |
索引在每个内部节点上指定将要保持空闲的空间。 1 = True |
|
IsPageLockDisallowed |
1 = 通过 sp_indexoption 禁用页锁定。 |
|
IsRowLockDisallowed |
1 = 通过 sp_indexoption 禁用行锁定。 |
|
IsStatistics |
索引是由 CREATE STATISTICS 语句或由 sp_dboption 的 auto create statistics 选项创建的。对于列级统计，统计索引将用作占位符。 1 = True |
|
IsUnique |
索引是唯一的。 1 = True |
返回类型
int
示例
下面的示例为 authors 表的 UPKCL_auidind 索引返回 IsPadIndex 属性的设置。
USE pubs
SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'UPKCL_auidind',
'IsPadIndex')
20OBJECT_ID
返回数据库对象标识号。
语法
OBJECT_ID ( 'object' )
参数
'object'
要使用的对象。object 的数据类型为 char 或 nchar。如果 object 的数据类型是 char，那么隐性将其转换成 nchar。
返回类型
int
注释
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。
如果指定一个临时表名，则必须在临时表名前面加上数据库名，例如：
SELECT OBJECT_ID('tempdb..#mytemptable')
系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。有关更多信息，请参见表达式和 WHERE。
示例
下面的示例为 pubs 数据库中的 authors 表返回对象 ID。
USE master
SELECT OBJECT_ID('pubs..authors')
下面是结果集：
-----------
1977058079
(1 row(s) affected)
21OBJECT_NAME
返回数据库对象名。
语法
OBJECT_NAME ( object_id )
参数
object_id
要使用的对象的 ID。object_id 的数据类型为 int。
返回类型
nchar
注释
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。
系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。有关更多信息，请参见表达式和 WHERE。
示例
下面的示例为 pubs 数据库中的 authors 表返回 OBJECT_NAME。
USE pubs
SELECT TABLE_CATALOG, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(1977058079)
下面是结果集：
TABLE_CATALOG TABLE_NAME
------------------------------ --------------
pubs authors
(1 row(s) affected)
22OBJECTPROPERTY
返回当前数据库中对象的有关信息。
语法
OBJECTPROPERTY ( id , property )
参数
id
一个表达式，包含当前数据库中某个对象的 ID。id 的数据类型是 int。
Property
一个表达式，包含针对由 id 指定的对象将要返回的信息。Property 可以是下面这些值中的一个。
说明 除非加以注释，否则，如果 property 是无效的属性名，则返回 NULL。
|
属性名称 |
对象类型 |
描述和返回的值 |
|
CnstIsClustKey |
约束 |
带有聚集索引的主键。 1 = True |
|
CnstIsColumn |
约束 |
COLUMN 约束。 1 = True |
|
CnstIsDeleteCascade |
约束 |
带有 ON DELETE CASCADE 选项的外键约束。 |
|
CnstIsDisabled |
约束 |
禁用的约束。 1 = True |
|
CnstIsNonclustKey |
约束 |
带有非聚集索引的主键。 1 = True |
|
CnstIsNotTrusted |
约束 |
启用约束时未检查现有行，所以可能不是所有行都受约束的控制。 1 = True |
|
CnstIsNotRepl |
约束 |
使用 NOT FOR REPLICATION 关键字定义约束。 |
|
CnstIsUpdateCascade |
约束 |
带有 ON UPDATE CASCADE 选项的外键约束。 |
|
ExecIsAfterTrigger |
触发器 |
AFTER 触发器。 |
|
ExecIsAnsiNullsOn |
过程、触发器、视图 |
创建时的 ANSI_NULLS 设置。 1 = True |
|
ExecIsDeleteTrigger |
触发器 |
DELETE 触发器。 1 = True |
|
ExecIsFirstDeleteTrigger |
触发器 |
对表执行 DELETE 时触发的第一个触发器。 |
|
ExecIsFirstInsertTrigger |
触发器 |
对表执行 INSERT 时触发的第一个触发器。 |
|
ExecIsFirstUpdateTrigger |
触发器 |
对表执行 UPDATE 时触发的第一个触发器。 |
|
ExecIsInsertTrigger |
触发器 |
INSERT 触发器。 1 = True |
|
ExecIsInsteadOfTrigger |
触发器 |
INSTEAD OF 触发器。 |
|
ExecIsLastDeleteTrigger |
触发器 |
对表执行 DELETE 时触发的最后一个触发器。 |
|
ExecIsLastInsertTrigger |
触发器 |
对表执行 INSERT 时触发的最后一个触发器。 |
|
ExecIsLastUpdateTrigger |
触发器 |
对表执行 UPDATE 时触发的最后一个触发器。 |
|
ExecIsQuotedIdentOn |
过程、触发器、视图 |
创建时的 QUOTED_IDENTIFIER 设置。 1 = True |
|
ExecIsStartup |
过程 |
启动过程。 1 = True |
|
ExecIsTriggerDisabled |
触发器 |
禁用的触发器。 1 = True |
|
ExecIsUpdateTrigger |
触发器 |
UPDATE 触发器。 1 = True |
|
HasAfterTrigger |
表，视图 |
表或视图具有 AFTER 触发器。 1 = True |
|
HasInsertTrigger |
表，视图 |
表或视图具有 INSERT 触发器。 1 = True |
|
HasInsteadOfTrigger |
表、视图 |
表或视图具有 INSTEAD OF 触发器。 1 = True |
|
HasUpdateTrigger |
表、视图 |
表或视图具有 UPDATE 触发器。 1 = True |
|
IsAnsiNullsOn |
函数、过程、表、触发器、视图 |
指定表的 ANSI NULLS 选项设置为 ON，表示所有与空值的比较都取值为 UNKNOWN。只要表存在，该设置就应用于表定义中的所有表达式，包括计算列和约束。 1 = ON |
|
IsCheckCnst |
任何 |
CHECK 约束。 1 = True |
|
IsConstraint |
任何 |
约束。 1 = True |
|
IsDefault |
任何 |
绑定的默认值。 1 = True |
|
IsDefaultCnst |
任何 |
DEFAULT 约束。 1 = True |
|
IsDeterministic |
函数、视图 |
函数的确定性属性。只适用于标量值及表值函数。 1 = 可确定的 |
|
IsExecuted |
任何 |
指定执行该对象的方式（视图、过程或触发器）。 1 = True |
|
IsExtendedProc |
任何 |
扩展过程。 1 = True |
|
IsForeignKey |
任何 |
FOREIGN KEY 约束。 1 = True |
|
IsIndexed |
表、视图 |
带有索引的表或视图。 |
|
IsIndexable |
表、视图 |
可以创建索引的表或视图。 |
|
IsInlineFunction |
函数 |
内嵌函数。 1 = 内嵌函数 |
|
IsMSShipped |
任何 |
在安装 Microsoft® SQL Server™ 2000 的过程中创建的对象。 1 = True |
|
IsPrimaryKey |
任何 |
PRIMARY KEY 约束。 1 = True |
|
IsProcedure |
任何 |
过程。 1 = True |
|
IsQuotedIdentOn |
函数、过程、表、触发器、视图 |
指定表的被引用标识符设置为 ON，表示在表定义所涉及的所有表达式中，双引号标记分隔标识符。 1 = ON |
|
IsReplProc |
任何 |
复制过程。 1 = True |
|
IsRule |
任何 |
绑定的规则。 1 = True |
|
IsScalarFunction |
函数 |
标量值函数。 1 = 标量值 |
|
IsSchemaBound |
函数，视图 |
使用 SCHEMABINDING 创建的架构绑定函数或视图。 1 = 架构绑定 |
|
IsSystemTable |
表 |
系统表。 1 = True |
|
IsTable |
表 |
表。 1 = True |
|
IsTableFunction |
函数 |
表值函数。 1 = 表值 |
|
IsTrigger |
任何 |
触发器。 1 = True |
|
IsUniqueCnst |
任何 |
UNIQUE 约束。 1 = True |
|
IsUserTable |
表 |
用户定义的表。 1 = True |
|
IsView |
视图 |
视图。 1 = True |
|
OwnerId |
任何 |
对象的所有者。 Nonnull = 对象所有者的数据库用户 ID。 |
|
TableDeleteTrigger |
表 |
表有 DELETE 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
|
TableDeleteTriggerCount |
表 |
表具有指定数目的 DELETE 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
|
TableFullTextBackgroundUpdateIndexOn |
表 |
表已启用全文后台更新索引。 1 = True |
|
TableFulltextCatalogId |
表 |
表的全文索引数据所驻留的全文目录的 ID。 Nonzero = 全文目录 ID，它与标识全文索引表中行的唯一索引相关。 |
|
TableFullTextChangeTrackingOn |
表 |
表已启用全文更改跟踪。 1 = True |
|
TableFulltextKeyColumn |
表 |
与某个单列唯一索引相关联的列 ID，这个单列唯一索引参与全文索引定义。 0 = 表不是全文索引的。 |
|
TableFullTextPopulateStatus |
表 |
0 = 不填充 |
|
TableHasActiveFulltextIndex |
表 |
表具有一个活动的全文索引。 1 = True |
|
TableHasCheckCnst |
表 |
表具有 CHECK 约束。 1 = True |
|
TableHasClustIndex |
表 |
表具有聚集索引。 1 = True |
|
TableHasDefaultCnst |
表 |
表具有 DEFAULT 约束。 1 = True |
|
TableHasDeleteTrigger |
表 |
表具有 DELETE 触发器。 1 = True |
|
TableHasForeignKey |
表 |
表具有 FOREIGN KEY 约束。 1 = True |
|
TableHasForeignRef |
表 |
表由 FOREIGN KEY 约束引用。 1 = True |
|
TableHasIdentity |
表 |
表具有标识列。 1 = True |
|
TableHasIndex |
表 |
表具有一个任何类型的索引。 1 = True |
|
TableHasInsertTrigger |
表 |
对象具有 Insert 触发器。 1 = True |
|
TableHasNonclustIndex |
表 |
表具有非聚集索引。 1 = True |
|
TableHasPrimaryKey |
表 |
表具有主键。 1 = True |
|
TableHasRowGuidCol |
表 |
对于 uniqueidentifier 列，表具有 ROWGUIDCOL。 1 = True |
|
TableHasTextImage |
表 |
表具有 text 列。 1 = True |
|
TableHasTimestamp |
表 |
表具有 timestamp 列。 1 = True |
|
TableHasUniqueCnst |
表 |
表具有 UNIQUE 约束。 1 = True |
|
TableHasUpdateTrigger |
表 |
对象具有 Update 触发器。 1 = True |
|
TableInsertTrigger |
表 |
表具有 INSERT 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
|
TableInsertTriggerCount |
表 |
表具有指定数目的 INSERT 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
|
TableIsFake |
表 |
表不是真实的。根据需要 SQL Server 对其进行内部具体化。 1 = True |
|
TableIsPinned |
表 |
驻留表以将其保留在数据高速缓存中。 1 = True |
|
TableTextInRowLimit |
表 |
text in row 所允许的最大字节数，如果没有设置 text in row 选项则为 0。 |
|
TableUpdateTrigger |
表 |
表具有 UPDATE 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
|
TableUpdateTriggerCount |
表 |
表具有指定数目的 UPDATE 触发器。 >1 = 给定类型的第一个触发器的 ID。 |
返回类型
int
注释
OBJECTPROPERTY(view_id,'IsIndexable') 可能会耗费大量的计算机资源，这是因为对 IsIndexable 属性的评估需要分析视图定义、进行规范化以及部分优化。
当至少添加了表的一列以用于索引时，OBJECTPROPERTY(table_id, 'TableHasActiveFulltextIndex') 将返回"1"(True)。只要添加了用于索引的第一列后，全文索引即可用于填充。
当除去索引中的最后一列时，索引变成非活动。
如果某些索引键需求条件得不到满足，那么实际创建索引仍然可能会失败。详细信息请参见 CREATE INDEX。
示例
A. 查明 authors 是否为一个表
下面的示例测试 authors 是否为一个表。
IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 1
print 'Authors is a table'
ELSE IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 0
print 'Authors is not a table'
ELSE IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') IS NULL
print 'ERROR: Authors is not an object'
B. 确定是否在表上启用了 text in row
下面的示例测试是否在 authors 表上启用了 text in row 选项，以便 text、ntext 或 image 数据可以存储在它的数据行内。
USE pubs
SELECT OBJECTPROPERTY(OBJECT_ID('authors'),'TableTextInRowLimit')
结果集显示在表上没有启用 text in row。
-----
0
C. 确定用户定义的标量值函数是否具有确定性
下面的示例测试用户定义的标量值函数 fn_CubicVolume 是否具有确定性，该函数返回小数。
CREATE FUNCTION fn_CubicVolume
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
--Is it a deterministic function?
SELECT OBJECTPROPERTY(OBJECT_ID('fn_CubicVolume'), 'IsDeterministic')
结果集显示 fn_CubicVolume 是确定性函数。
-----
1
23@@PROCID
返回当前过程的存储过程标识符 (ID) 。
语法
@@PROCID
返回类型
integer
示例
下面的示例创建了一个过程，在此过程内用 SELECT 显示 @@PROCID 设置。
CREATE PROCEDURE testprocedure AS
SELECT @@PROCID AS 'ProcID'
GO
EXEC testprocedure
GO
24SQL_VARIANT_PROPERTY
返回有关 sql_variant 值的基本数据类型和其它信息。
语法
SQL_VARIANT_PROPERTY ( expression, property )
参数
expression
是 sql_variant 类型的表达式。
property
包含将为其提供信息的 sql_variant 属性名称。property 是 varchar(128)，可以是下列任何值。
|
值 |
描述 |
返回的 sql_variant 基本类型 |
|
BaseType |
SQL Server 数据类型，如： char |
sysname 无效的输入 = NULL |
|
Precision |
数字基本数据类型的位数： datetime = 23 |
int 无效的输入 = NULL |
|
Scale |
数字基本数据类型小数点右边的位数： decimal (p,s) 和 numeric (p,s) = s |
int 无效的输入 = NULL |
|
TotalBytes |
要包含值的元数据和数据所需的字节数。该信息在检查 sql_variant 列中数据的最大一侧时很有用。如果该值大于 900，索引创建将失败。 |
int 无效的输入 = NULL |
|
Collation |
代表特定 sql_variant 值的排序规则。 |
sysname 无效的输入 = NULL |
|
MaxLength |
最大数据类型长度（以字节为单位）。例如，nvarchar(50) 的 MaxLength 是 100，int 的 MaxLength 是 4。 |
int 无效的输入 = NULL |
返回类型
sql_variant
示例
下例假定 tableA 包含 colB 和属于 sql_variant 类型的 colA，检索有关值为 46279.1 的 colA（其中 colB =1689）的 SQL_VARIANT_PROPERTY 信息。
CREATE TABLE tableA(colA sql_variant, colB int)
INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)
SELECT SQL_VARIANT_PROPERTY(colA,'BaseType'),
SQL_VARIANT_PROPERTY(colA,'Precision'),
SQL_VARIANT_PROPERTY(colA,'Scale')
FROM tableA
WHERE colB = 1689
下面是结果集。（注意这三个值每个都是 sql_variant。)
|
decimal |
8 |
2 |
25TYPEPROPERTY
返回有关数据类型的信息。
语法
TYPEPROPERTY ( type , property )
参数
type
是数据类型的名称。
property
是要返回的数据类型的信息类型。property可以是下列值之一：
|
属性 |
描述 |
返回的值 |
|
Precision |
数据类型的精度。 |
数字位数或字符个数。 NULL = 数据类型未找到。 |
|
Scale |
数据类型的小数位数。 |
数据类型的小数位的个数。 NULL = 数据类型不是 numeric 或未找到。 |
|
AllowsNull |
数据类型允许空值。 |
1 = True |
|
UsesAnsiTrim |
创建数据类型时 ANSI 填充设置为 ON。 |
1 = True |
返回类型
int
示例
下例返回 integer 数据类型的精度或位数。
SELECT TYPEPROPERTY( 'tinyint', 'PRECISION')
申明
非源创博文中的内容均收集自网上，若有侵权之处，请及时联络，我会在第一时间内删除．再次说声抱歉!!!
博文欢迎转载，但请给出原文连接。
