----系统表--------------------------------------------------------------------------------------
虽然使用系统存储过程、系统函数与信息架构视图已经可以为我们提供了相当丰富的元数据信息,但是对于某些特殊的元数据信息,我们仍然需要直接对系统表进行查询。因为SQL Server 将所有数据库对象的信息均存放在系统表中,作为 SQL Server 的管理、开发人员,了解各个系统表的作用将有助于我们了解 SQL Server 的内在工作原理。
SQL Server 的系统表非常多,其中最常用的与元数据查询有关的表有如下一些:
系统表
|
描述
|
syscolumns
|
存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。
|
syscomments
|
存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。
|
sysconstraints
|
存储当前数据库中每一个约束的基本信息。
|
sysdatabases
|
存储当前服务器上每一个数据库的基本信息。
|
sysindexes
|
存储当前数据库中的每个索引的信息。
|
sysobjects
|
存储数据库内的每个对象(约束、默认值、日志、规则、存储过程等)的基本信息。
|
sysreferences
|
存储所有包括 FOREIGN KEY 约束的列。
|
systypes
|
存储系统提供的每种数据类型和用户定义数据类型的详细信息。
|
将系统存储过程、系统函数、信息架构视图与系统表结合使用,可以方便地让我们获得所有需要的元数据信息。
示例:
1、 获得当前数据库所有用户表的名称。
SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
其中主要用到了系统表 sysobjects以及其属性 xtype,还有就是用到了 OBJECTPROPERTY 系统函数来判断是不是安装 SQL Server 的过程中创建的对象。
2、 获得指定表上所有的索引名称
SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0
----系统存储过程---------------------------------------------------------------------------
系统存储过程
|
描述
|
sp_columns
|
返回指定表或视图的列的详细信息。
|
sp_databases
|
返回当前服务器上的所有数据库的基本信息。
|
sp_fkeys
|
若参数为带有主键的表,则返回包含指向该表的外键的所有表;若参数为带有外键的表名,则返回所有同过主键/外键关系与该外键相关联的所有表。
|
sp_pkeys
|
返回指定表的主键信息。
|
sp_server_info
|
返回当前服务器的各种特性及其对应取值。
|
sp_sproc_columns
|
返回指定存储过程的的输入、输出参数的信息。
|
sp_statistics
|
返回指定的表或索引视图上的所有索引以及统计的信息。
|
sp_stored_procedures
|
返回当前数据库的存储过程列表,包含系统存储过程。
|
sp_tables
|
返回当前数据库的所有表和视图,包含系统表。
|
----系统函数----------------------------------------------------------------------------------------------------
COLUMNPROPERTY
|
返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。
|
COL_LENGTH
|
返回指定数据库的指定属性值,如是否处于只读模式等。
|
DATABASEPROPERTYEX
|
返回指定数据库的指定选项或属性的当前设置,如数据库的状态、恢复模型等。
|
OBJECT_ID
|
返回指定数据库对象名的标识号
|
OBJECT_NAME
|
返回指定数据库对象标识号的对象名。
|
OBJECTPROPERTY
|
返回指定数据库对象标识号的有关信息,如是否为表,是否为约束等。
|
fn_listextendedproperty
|
返回数据库对象的扩展属性值,如对象描述、格式规则、输入掩码等。
|
----使用信息架构视图访问元数据---------------------------------------------------------------------------------------------------
信息架构视图基于 SQL-92 标准中针对架构视图的定义,这些视图独立于系统表,提供了关于 SQL Server 元数据的内部视图。信息架构视图的最大优点是,即使我们对系统表进行了重要的修改,应用程序也可以正常地使用这些视图进行访问。因此对于应用程序来说,只要是符合 SQL-92 标准的数据库系统,使用信息架构视图总是可以正常工作的。
常用的信息架构视图有以下一些:
信息架构视图
|
描述
|
INFORMATION_SCHEMA .CHECK_CONSTRAINTS
|
返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。
|
INFORMATION_SCHEMA .COLUMNS
|
返回当前数据库中当前用户可以访问的所有列及其基本信息。
|
INFORMATION_SCHEMA .CONSTRAINT_COLUMN_USAGE
|
返回当前数据库中定义了约束的所有列及其约束名。
|
INFORMATION_SCHEMA .CONSTRAINT_TABLE_USAGE
|
返回当前数据库中定义了约束的所有表及其约束名。
|
INFORMATION_SCHEMA .KEY_COLUMN_USAGE
|
返回当前数据库中作为主键/外键约束的所有列。
|
INFORMATION_SCHEMA .SCHEMATA
|
返回当前用户具有权限的所有数据库及其基本信息。
|
INFORMATION_SCHEMA .TABLES
|
返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。
|
INFORMATION_SCHEMA .VIEWS
|
返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。
|
由于这些信息架构都是以视图的方式存在的,因此我们可以很方便地获得并利用需要的信息。
例如,我们要得到某个表有多少列,可以使用以下语句:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
----应用----------------------------------------------------------------------
--1:获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
--2:获取某一个表的所有字段
select name from syscolumns where id=object_id(N'表名')
--3:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like N'%表名%'
--4:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
--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 = N'表名'
--7:获取数据库文件路径
select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('MDF',filename)>0
or
select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('LDF',filename)>0
--8:获取某一个表的基本信息
sp_MShelpcolumns N'表名'
--9:获取某一个表的主键、外键信息
exec sp_pkeys N'表名'
exec sp_fkeys N'表名'
--10:判断某一个表是否存在某一列(字段)
if exists(select 1 from syscolumns where id=object_id(N'表名) and name=N'字段')
print N'存在'
else
print N'不存在'
下面给出了一个存储过程,它的作用是自动将当前数据库的用户存储过程加密。
DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = 'P'
AND type = 'P'
AND crdate < @now
AND OBJECTPROPERTY(id, 'IsMSShipped')=0
OPEN sp_cursor
FETCH NEXT FROM sp_cursor
INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin = PATINDEX ( '%AS' + char(13) + '%', @sp_content)
SELECT @sp_content = SUBSTRING(@sp_content, 1, @asbegin - 1)
+ ' WITH ENCRYPTION AS'
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = 'DROP PROCEDURE [' + @sp_name + ']'
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
该存储过程利用了 sysobjects 和 syscomments 表,并巧妙地修改了原存储过程的 SQL 定义语句,将 AS 修改为了 WITH ENCRYPTION AS,从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。
<来自:http://www.cnblogs.com/Spring/archive/2008/05/28/1209092.html>
分享到:
相关推荐
第1章 简介 第2章 SQL Server环境 第3章 存储过程的设计概念 第4章 T-SQL的基本编程结构 第5章 函数 第6章 复合T-SQL结构:批处理、脚本和事务 第7章 调试和错误处理 ...附录 SQL Server 2000中的T-SQL与XML数据类型
10.1.3 使用sp_executesql存储过程 10.2 使用时间戳的乐观锁 10.2.1 时间戳 10.2.2 TSEQUAL函数 10.2.3 时间戳转换 10.3 全文搜索和索引 10.4 嵌套的存储过程 10.4.1 使用临时表来传递一个记录集给一个嵌套的存储...
Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能,使您可以构建和管理用于业务的高可用和高性能的数据应用程序。 文档整理了一些常用的SQL函数,在开发中如果可以用上的话可以...
本书全面系统地介绍了SQL Server开发和管理的应用技术,涉及安装和配置SQL Server、日期处理、字符处理、排序规则、编号处理、数据统计与汇总、分页处理、树形数据处理、数据导入与导出、作业、数据备份与还原、用户...
10.1.3 使用sp_executesql存储过程 10.2 使用时间戳的乐观锁 10.2.1 时间戳 10.2.2 TSEQUAL函数 10.2.3 时间戳转换 10.3 全文搜索和索引 10.4 嵌套的存储过程 10.4.1 使用临时表来传递一个记录集给一个嵌套的存储...
存储过程(Stored Procedure)是一组已经事先编译好,能够完成特定功能的SQL语句集合,是一个独立的数据库对象。存储过程类似于高级语言中的函数,可被用户的应用程序调用,执行。不同的是,存储过程经过编译后存放在...
│ 7.2.4 使用系统存储过程实现的通用分页存储过程.sql │ │ 7.3.1 实现随机分页的通用分页存储过程.sql │ │ 7.3.2 根据分类表实现的分页存储过程.sql │ │ │ └─其他 │ sp_cursor.sql...
SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表,这两个表的逻辑结构与被触发器作用的表一样,用户可以读取这两个表的内容,但不能对它们进行修改,触发器执行完后,这两个表也会自动删除。...
常用SQL查询技术、比较逻辑运算符查询、SQL关键字查询、表结构与性能、时间与谓词、聚合函数、子查询与连接查询、交叉表、常用数据操作高级应用、试图存储过程和触发器的使用。。。。。。。
存储过程分类1>系统存储过程2>自定义存储过程3>扩展存储过程3.调用存储过程4.创建自定义存储过程5.存储过程优点三.SQL注入1.SQL注入概念2.如何防止SQL注入(1)不要使用动态SQL(2)不要将敏感数据保留在纯文本中(3)限制...
本书全面介绍 Microsoft SQL Server 2000 数据库应用系统的开发技术。全书共12 章,主要内容包括: SQL Server 2000 的安装和配置,SQL Server 2000 数据类型,数据库和数据表,数据库的查询,索引与数据 完整性,...
SQL Server 2005中ROW_NUMBER()函数在存储过程分页中的应用.pdf
典型的数据库编程对象包括视图、存储过程、触发器、函数等。视图在第10章已经讲过了,本章将讲述其他常用的数据库编程对象。存储过程是一个可重用的代码模块,可以高效率地完成指定的操作。触发器是一种特殊类型的...
Transact-SQL是微软对SQL的扩展,具有SQL的主要特点,同时增加了变量、运算符、函数、流程控制和注释等语言元素,使得其功能更加强大。而存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。存储...
选定某一主题,运用oracle数据库,以及相关技术...2、块、游标、储存过程、函数、触发器等的设计及调用正确; 3、报告文档说明条理清晰,格式正确,写作规范 4、系统运行良好,有一定的独立性与创新性,态度积极认真;
涉及到SQLServer2000、SQLServer2005、Access、Oracle10g、MySql5.0和Postgresql8.2等6种数据库语言,内容包括SQL语言基础、常规数据查询、高级数据过滤、字符串查询、日期函数、日期函数、数据排序、聚集函数与分组...
它的目的在于能够方便的从系统表中查询信息,或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下,一些代码会被开发者重复编写多次,...
4.2.2 插入测试表调试存储过程 38 4.2.3 DBMS_OUTPUT系统内置包 39 附录一 sql*plus工具 40 附录1.1 sql*plus启动和关闭 41 附录1.2 sql*plus 环境设置 42 附录1.3 设置环境参数 42 附录1.4 sqlplus命令的执行 43 ...