Transact-SQL 从零开始

总结了一部分Transact-SQL的基础知识，自己留着慢慢看吧！

sysobjects

在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。

列名	数据类型	描述
name	sysname	对象名。
Id	int	对象标识号。
xtype	char(2)	对象类型。可以是下列对象类型中的一种： 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	所有者对象的用户 ID。
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	保留。

 

sys.sysindexkeys (Transact-SQL)

看来微软是打算抛弃他了

包含有关数据库的索引中的键或列的信息。

重要提示：
将此 SQL Server 2000 系统表作为一个视图包含进来是为了保持向后兼容性。建议您改用当前的 SQL Server 系统视图。若要查找一个或多个等效系统视图，请参阅将 SQL Server 2000 系统表映射到 SQL Server 2005 系统视图。后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。
列名	数据类型	说明
id	int	表的 ID。
indid	smallint	索引的 ID。
colid	smallint	列的 ID。
keyno	smallint	该列在索引中的位置。

 

syscolumns

每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行。该表位于每个数据库中。

列名	数据类型	描述
name	sysname	列名或过程参数的名称。
id	int	该列所属的表对象 ID，或与该参数关联的存储过程 ID。
xtype	tinyint	systypes 中的物理存储类型。
typestat	tinyint	仅限内部使用。
xusertype	smallint	扩展的用户定义数据类型 ID。
length	smallint	systypes 中的最大物理存储长度。
xprec	tinyint	仅限内部使用。
xscale	tinyint	仅限内部使用。
colid	smallint	列或参数 ID。
xoffset	smallint	仅限内部使用。
bitpos	tinyint	仅限内部使用。
reserved	tinyint	仅限内部使用。
colstat	smallint	仅限内部使用。
cdefault	int	该列的默认值 ID。
domain	int	该列的规则或 CHECK 约束 ID。
number	smallint	过程分组时（0 表示非过程项）的子过程号。
colorder	smallint	仅限内部使用。
autoval	varbinary(255)	仅限内部使用。
offset	smallint	该列所在行的偏移量；如果为负，表示可变长度行。
status	tinyint	用于描述列或参数属性的位图： 0x08 = 列允许空值。 0x10 = 当添加 varchar 或 varbinary 列时，ANSI 填充生效。保留 varchar 列的尾随空格，保留 varbinary 列的尾随零。 0x40 = 参数为 OUTPUT 参数。 0x80 = 列为标识列。
type	tinyint	systypes 中的物理存储类型。
usertype	smallint	systypes 中的用户定义数据类型 ID。
printfmt	varchar(255)	仅限内部使用。
prec	smallint	该列的精度级别。
scale	int	该列的小数位数。
iscomputed	int	表示是否已计算该列的标志： 0 = 未计算。 1 = 已计算。
isoutparam	int	表示该过程参数是否是输出参数： 1 = 真。 0 = 假。
isnullable	int	表示该列是否允许空值： 1 = 真。 0 = 假。

systypes

对于每种系统提供数据类型和用户定义数据类型，均包含一行信息。该表存储在每个数据库中。

这些是系统提供的数据类型及其 ID 号。

列名	数据类型	描述
name	sysname	数据类型名称。
xtype	tinyint	物理存储类型。
status	tinyint	仅限内部使用。
xusertype	smallint	扩展用户类型。
length	smallint	数据类型的物理长度。
xprec	tinyint	服务器所使用的内部精度。（不能在查询中使用。）
xscale	tinyint	服务器所使用的内部小数位数。（不能在查询中使用。）
tdefault	int	对此数据类型进行完整性检查的存储过程的 ID。
domain	int	对此数据类型进行完整性检查的存储过程的 ID。
uid	smallint	数据类型创建者的用户 ID。
reserved	smallint	仅限内部使用。
usertype	smallint	用户类型 ID。
variable	bit	可变长度数据类型为 1；否则为 0。
allownulls	bit	指出此数据类型的默认为空性。如果 CREATE 或 ALTER TABLE 指定了为空性，那么该值将替代此数据类型的默认为空性。
type	tinyint	物理存储数据类型。
printfmt	varchar(255)	保留。
prec	smallint	此数据类型的精度级别。
scale	tinyint	此数据类型的小数位数（根据精度）。

 

 

 

 

 

获取系统表信息-2000

SELECT 

表名=CASE WHEN a.colorder = 1 THEN d.name ELSE '' END, 

表说明=CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END , 

字段序号=a.colorder , 

字段名=a.name , 

标识=CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END , 

主键=CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN 

    dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN 

    dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN 

    dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' 

    WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END , 

类型=b.name , 

长度=a.length , 

精度=COLUMNPROPERTY(a.id, a.name, 'PRECISION') , 

小数位数=ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) , 

允许空=CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END , 

默认值= ISNULL(e.text, ''), 

字段说明=ISNULL(g.[value], ''), 

创建时间=d.crdate, 

更改时间=CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END 

FROM dbo.syscolumns a LEFT OUTER JOIN 

dbo.systypes b ON a.xtype = b.xusertype INNER JOIN 

dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND 

d.status >= 0 LEFT OUTER JOIN 

dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN 

dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND 

g.name = 'MS_Description' LEFT OUTER JOIN

dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND 

f.name = 'MS_Description'

ORDER BY d.id, a.colorder

 

 

--而且内容

 

--1：获取当前数据库中的所有用户表

 

select Name from sysobjects where xtype='u' and status>=0

 

--2：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

 

--3：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

 

--4：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P' and name not like 'sp_%'--过滤系统存储过程

 

--5：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

 

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

--6：查询某一个表的字段和数据类型

 

select column_name,data_type from information_schema.columns

where table_name = '表名'

 

--7:查询某张表的主键（参考SqlServer系统存储过程sp_pkeys）

 

select COLUMN_NAME = convert(sysname,c.name)

from sysindexes i, syscolumns c, sysobjects o

where o.id = object_id('[YourTableName]')

and o.id = c.id

and o.id = i.id

and (i.status & 0x800) = 0x800

and (c.name = index_col ('[YourTableName]', i.indid, 1) or

c.name = index_col ('[YourTableName]', i.indid, 2) or

c.name = index_col ('[YourTableName]', i.indid, 3) or

c.name = index_col ('[YourTableName]', i.indid, 4) or

c.name = index_col ('[YourTableName]', i.indid, 5) or

c.name = index_col ('[YourTableName]', i.indid, 6) or

c.name = index_col ('[YourTableName]', i.indid, 7) or

c.name = index_col ('[YourTableName]', i.indid, 8) or

c.name = index_col ('[YourTableName]', i.indid, 9) or

c.name = index_col ('[YourTableName]', i.indid, 10) or

c.name = index_col ('[YourTableName]', i.indid, 11) or

c.name = index_col ('[YourTableName]', i.indid, 12) or

c.name = index_col ('[YourTableName]', i.indid, 13) or

c.name = index_col ('[YourTableName]', i.indid, 14) or

c.name = index_col ('[YourTableName]', i.indid, 15) or

c.name = index_col ('[YourTableName]', i.indid, 16))

 

--7:查询某张表的外键

select t1.name,t2.rtableName,t2.name

from (select col.name, f.constid as temp

from syscolumns col,sysforeignkeys f

where f.fkeyid=col.id

and f.fkey=col.colid

and f.constid in (select distinct(id)

from sysobjects

where OBJECT_NAME(parent_obj)='YourTableName'

and xtype='F')

)as t1 ,

(select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp

from syscolumns col,sysforeignkeys f

where f.rkeyid=col.id

and f.rkey=col.colid

and f.constid in (select distinct(id)

from sysobjects

where OBJECT_NAME(parent_obj)='YourTableName'

and xtype='F')

) as t2

where t1.temp=t2.temp

 

 

 

-2005

SELECT

表名=CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,

表说明=CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END ,

字段序号=a.colorder ,

字段名=a.name ,

标识=CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END ,

主键=CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN

    dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN

    dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN

    dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'

    WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END ,

类型=b.name ,

长度=a.length ,

精度=COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,

小数位数=ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,

允许空=CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END ,

默认值= ISNULL(e.text, ''),

字段说明=ISNULL(g.[value], ''),

创建时间=d.crdate,

更改时间=CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END

    FROM dbo.syscolumns a LEFT OUTER JOIN

    dbo.systypes b ON a.xtype = b.xusertype INNER JOIN

    dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND

    d.status >= 0 LEFT OUTER JOIN

    dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN

    sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN

    sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0

    ORDER BY d.id, a.colorder