--sql server 数表:
select count(1) from sysobjects where xtype='U'
--数视图:
select count(1) from sysobjects where xtype='V'
--数存储过程
select count(1) from sysobjects where xtype='P'
SELECT * FROM sysobjects WHERE (xtype = 'U')
--C = CHECK 约束
--D = 默认值或 DEFAULT 约束
--F = FOREIGN KEY 约束
--L = 日志
--FN = 标量函数
--IF = 内嵌表函数
--P = 存储过程
--PK = PRIMARY KEY 约束(类型是 K)
--RF = 复制筛选存储过程
--S = 系统表
--TF = 表函数
--TR = 触发器
--U = 用户表
--UQ = UNIQUE 约束(类型是 K)
--V = 视图
--X = 扩展存储过程
SQL Server xtype
的介绍
在数据库内创建的每个对象(约束、默认值、日志、规则、存储过程等)在表中占一行。只有在 tempdb
内,每个临时对象才在该表中占一行。
列名 | 数据类型 | 描述 |
---|---|---|
name | sysname | 对象名,常用列 |
id | int | 对象标识号 |
xtype | char(2) | 对象类型。常用列。xtype可以是下列对象类型中的一种: |
AF = Aggregate function (CLR) | ||
C = CHECK constraint | ||
D = Default or DEFAULT constraint | ||
F = FOREIGN KEY constraint | ||
L = Log | ||
FN = Scalar function | ||
FS = Assembly (CLR) scalar-function | ||
FT = Assembly (CLR) table-valued function | ||
IF = In-lined table-function | ||
IT = Internal table | ||
P = Stored procedure | ||
PC = Assembly (CLR) stored-procedure | ||
PK = PRIMARY KEY constraint (type is K) | ||
RF = Replication filter stored procedure | ||
S = System table | ||
SN = Synonym | ||
SQ = Service queue | ||
TA = Assembly (CLR) DML trigger | ||
TF = Table function | ||
TR = SQL DML Trigger | ||
TT = Table type | ||
U = User table | ||
UQ = UNIQUE constraint (type is K) | ||
V = View | ||
X = Extended stored procedure | ||
uid | smallint | Schema ID of the owner of the object. For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner. Overflows or returns NULL if the number of users and roles exceeds 32,767. |
** Important ** If you use any of the following SQL Server DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects. | ||
CREATE | ALTER | DROP USER | ||
CREATE | ALTER | DROP ROLE | ||
CREATE | ALTER | DROP APPLICATION ROLE | ||
CREATE SCHEMA | ||
ALTER AUTHORIZATION ON OBJECT | ||
info | smallint | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
status | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
base_schema_ver | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
replinfo | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
parent_obj | int | Object identification number of the parent object. For example, the table ID if it is a trigger or constraint. |
crdate | datetime | Date the object was created. |
ftcatid | smallint | Identifier of the full-text catalog for all user tables registered for full-text indexing, and 0 for all user tables that are not registered. |
schema_ver | int | Version number that is incremented every time the schema for a table changes. Always returns 0. |
stats_schema_ver | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
type | char(2) | Object type. Can be one of the following values: |
AF = Aggregate function (CLR) | ||
C = CHECK constraint | ||
D = Default or DEFAULT constraint | ||
F = FOREIGN KEY constraint | ||
FN = Scalar function | ||
FS = Assembly (CLR) scalar-function | ||
FT = Assembly (CLR) table-valued functionIF = In-lined table-function | ||
IT - Internal table | ||
K = PRIMARY KEY or UNIQUE constraint | ||
L = Log | ||
P = Stored procedure | ||
PC = Assembly (CLR) stored-procedure | ||
R = Rule | ||
RF = Replication filter stored procedure | ||
S = System table | ||
SN = Synonym | ||
SQ = Service queue | ||
TA = Assembly (CLR) DML trigger | ||
TF = Table function | ||
TR = SQL DML Trigger | ||
TT = Table type | ||
U = User table | ||
V = View | ||
X = Extended stored procedure | ||
userstat | smallint | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
sysstat | smallint | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
indexdel | smallint | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
refdate | datetime | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
version | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
deltrig | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
instrig | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
updtrig | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
seltrig | int | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
category | int | Used for publication, constraints, and identity. |
cache | smallint | Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
列名 | 数据类型 | 描述 |
---|---|---|
name | sysname | 对象名,常用列 |
id | int | 对象标识号 |
xtype | char(2) | 对象类型。常用列。xtype可以是下列对象类型中的一种: |
C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 | ||
IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束(类型是 K) RF = 复制筛选存储过程 | ||
S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束(类型是 K) | ||
V = 视图 X = 扩展存储过程 | ||
uid | smallint | 所有者用户对象编号 |
info | smallint | 保留。仅限内部使用 |
status | int | 保留。仅限内部使用 |
base_schema_ ver | int | 保留。仅限内部使用 |
replinfo | int | 保留。供复制使用 |
parent_obj | int | 父对象的对象标识号(例如,对于触发器或约束,该标识号为表 ID)。 |
crdate | datetime | 对象的创建日期。 |
ftcatid | smallint | 为全文索引注册的所有用户表的全文目录标识符,对于没有注册的所有用户表则为 0 |
schema_ver | int | 版本号,该版本号在每次表的架构更改时都增加。 |
stats_schema_ ver | int | 保留。仅限内部使用。 |
type | char(2) | 对象类型。可以是下列值之一: |
C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 | ||
FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束 | ||
L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程 | ||
S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程 | ||
userstat | smallint | 保留。 |
sysstat | smallint | 内部状态信息 |
indexdel | smallint | 保留 |
refdate | datetime | 留用 |
version | int | 保留 |
deltrig | int | 保留 |
instrig | int | 保留 |
updtrig | int | 保留 |
seltrig | int | 保留 |
category | int | 用于发布、约束和标识 |
cache | smallint | 保留 |
可以很方便的用
select * from sysobjects where xtype='TR' and parent_obj=object_id('表名')
查出表的信息
syscolumns
表内的xtype
查了一下,这些东西都是存于每一个数据库的syscolumns
表里面得,name
就是列名,xtype
就是数据类型,但是这个xtype
是数字的,下面是数字和数据类型对应的关系;
xtype=34 'image'
xtype= 35 'text'
xtype=36 'uniqueidentifier'
xtype=48 'tinyint'
xtype=52 'smallint'
xtype=56 'int'
xtype=58 'smalldatetime'
xtype=59 'real'
xtype=60 'money'
xtype=61 'datetime'
xtype=62 'float'
xtype=98 'sql_variant'
xtype=99 'ntext'
xtype=104 'bit'
xtype=106 'decimal'
xtype=108 'numeric'
xtype=122 'smallmoney'
xtype=127 'bigint'
xtype=165 'varbinary'
xtype=167 'varchar'
xtype=173 'binary'
xtype=175 'char'
xtype=189 'timestamp'
xtype=231 'nvarchar'
xtype=239 'nchar'
xtype=241 'xml'
xtype=231 'sysname'