数据字典总结(Sql server 2005)

2024-08-31 00:53:41

字体：大中小

来源：转载

供稿：网友

SQl server data dictionary
1 insert usage by using select keyWord

--插入表的方法
insert into testa

select 3,'test'

union all

select 4,'insert'

--系统存储过程
?1 内置的存储过程sp_help

?2 sp_helpconstraint fortest

?3 sp_rename 'csvimp.FIELD_6','gender','column'

?4 sp_renamedb 'testshenliang','test123';

?5 sp_dboption 'testshen'

EXEC sp_dboption @dbname = 'Test',

@optname = 'read only',

@optvalue = 'TRUE'

?6 sp_helptext 'ck_gender'

?7 sp_depends 'fortest'

?8 sp_rename 'test_name','test_allnames'

?9 sp_who and sp_who2

?10 sp_executesql EXEC sp_executesql N'SELECT * FROM Testshen.dbo.testc'

?11 sp_helpindex pbcattbl(table name)

?12 sp_executesql

EXEC sp_executesql N'PRINT @MyVariable'

?13 sp_helpsrvrole

?14 sp_srvrolepermission 服务器角色权限

?15 sp_helpdbfixedrole

?16 sp_dbfixedrolepermission

2 create check constraint

--创建约束
use Testshen

alter table fortest

add constraint ck_grade check(grade>=50 AND grade<=100)

3 drop a constraint

--删除约束
alter Testshen

drop constraint ck_grade;

4 query a

--查看表中前70%的记录
select top 70 percent * from fortest

5 compress a database

--压缩数据库的方法
go

dbcc shrinkdatabase ('testshen',20)

6 see tables created by youself

--查看设定条件下创建的表
SELECT * FROM sysobjects

where type='U'

and crdate>'2007.10.28'

7 see columns from a fixed table

--查看表中的字段
select name from syscolumns where id=object_id('fortest')

8.1 see all tables under a user

--查看用户下所有的表
select name from sysobjects where type='U';

Xtype的可能的值为
•C = CHECK constraint
•D = Default or DEFAULT constraint
•F = FOREIGN KEY constraint
•L = Log
•P = Stored procedure
•PK = PRIMARY KEY constraint (type is K)
•RF = Replication filter stored procedure
•S = System table
•TR = Trigger
•U = User table
•UQ = UNIQUE constraint (type is K)
•V = View
•X = Extended stored procedure
8.2用 sysobjects 查看一个数据库里的所有触发器
SELECT Sys2.[name] TableName,

Sys1.[name] TriggerName,

CASE WHEN Sys1.deltrig > 0 THEN'Delete'

WHEN Sys1.instrig > 0 THEN'Insert'

WHEN Sys1.updtrig > 0 THEN'Update'

END'TriggerType' FROM

sysobjects Sys1 JOIN sysobjects Sys2

ON Sys1.parent_obj = Sys2.[id] WHERE Sys1.xtype='TR'ORDER BY TableName
In sql server 2005 use this to im

9 see some tables by a indistinct inquire

--查看表名以”T”开头的所有表
select name from sysobjects where name like 'T%';

--查看表” T_B_SERVLEVEL”是不是存在
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='T_B_SERVLEVEL'

10 query some forward records by random way

--随机查看表中的前三条记录
select top 3 * from fortest order by newid();

11 select records from m to n(n is large than m) distict like(n,m]

--查看从m 到n的记录((n,m])

select top 2 * from(select top 5 * from fortest order by id asc)test order by id desc

select top 5 * from fortest order by id asc

select top 2 * from(select top 5 * from fortest order by id asc)test order by id desc

12 创建用户的信息如下:

sp_password null,'sa123456','sa'
13 检索表中字段中含有某个信息的方法(08 10 06)

select * from db_datareader.s where PATINDEX('%料%',sname) > 0

(即是检索字段Sname中含有”料”字的记录)
13.1 A.在 PATINDEX 中使用模式
USE AdventureWorks;

SELECT PATINDEX('%ensure%',DocumentSummary)

FROM Production.Document

WHERE DocumentID = 3;

13.2 B. 在 PATINDEX 中使用通配符
USE AdventureWorks;

SELECT PATINDEX('%en_ure%', DocumentSummary)

FROM Production.Document

WHERE DocumentID = 3;

13.3 C. 在 PATINDEX 中使用 COLLATE

USE tempdb;

SELECT PATINDEX ( '%ein%', 'Das ist ein Test' COLLATE Latin1_General_BIN) ;

13.4 D.查看address的值,如果有()取括号内的内容
select substring(address,patindex('%(________)%',address)+1,8) from tb22

where patindex('%(________)%',address) > 0

14 isnull()的使用
-- 如果「自述」字段没有数据则显示出文字‘没数据’

SELECT 姓名,ISNULL(自述,'没数据') FROM 雪山成品

GO
15 About user roles chinese description

--用户的角色信息
固定服务器角色描述
sysadmin 可以在SQL Server 中执行任何活动
serveradmin 可以设置服务器范围的配置选项关闭服务器
setupadmin 可以管理链接服务器和启动过程
securityadmin 可以管理登录和CREATE DATABASE 权限还可以读取错误日志和更改密码
processadmin 可以管理在SQL Server 中运行的进程
dbcreator 可以创建更改和除去数据库
diskadmin 可以管理磁盘文件
bulkadmin 可以执行BULK INSERT 语句
固定数据库角色描述
db_owner 在数据库中有全部权限
db_accessadmin 可以添加或删除用户ID

db_securityadmin 可以管理全部权限对象所有权角色和角色成员资格
db_ddladmin 可以发出ALL DDL但不能发出GRANT REVOK或DENY语句
db_backupOperator 可以发出DBCC CHECKPOINT和BACKUP 语句
db_datareader 可以选择数据库内任何用户表中的所有数据
db_datawriter 可以更改数据库内任何用户表中的所有数据
db_denydatareader 不能选择数据库内任何用户表中的任何数据
db_denydatawriter 不能更改数据库内任何用户表中的任何数据
16 创建数据库的语法
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Test')

DROP DATABASE Test

CREATE DATABASE Test

(

NAME = N'Test_Data',

FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/Test_Data.MDF' ,

SIZE = 1,

FILEGROWTH = 10%

)

LOG ON

(

NAME = N'Test_Log',

FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/Test_Log.LDF',

SIZE = 1,

FILEGROWTH = 10%

)

COLLATE Chinese_Taiwan_Stroke_CI_AS

-- 更改数据库的名称
EXEC sp_renamedb @dbname = 'Test', @newname = '我的范例数据库'

-- EXEC sp_renamedb 'Test', '我的范例数据库'
17 修改建立数据库语句,添加修改信息
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TestDB')

DROP DATABASE TestDB

CREATE DATABASE TestDB ON (NAME = N'TestDB_Data', FILENAME = N'C:/Temp/TestDB_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'TestDB_Log', FILENAME = N'C:/Temp/TestDB_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

COLLATE Chinese_Taiwan_Stroke_CI_AS

ALTER DATABASE TestDB ADD FILEGROUP UdfGroup

ALTER DATABASE TestDB ADD FILE(NAME = N'TestDB1', FILENAME = N'C:/Temp/TestDB1_Data.NDF' , SIZE = 1, FILEGROWTH = 10%) TO FILEGROUP UdfGroup

ALTER DATABASE TestDB ADD FILE(NAME = N'TestDB2', FILENAME = N'C:/Temp/TestDB2_Data.NDF' , SIZE = 1, FILEGROWTH = 10%) TO FILEGROUP UdfGroup

ALTER DATABASE TestDB ADD FILE(NAME = N'TestDB3', FILENAME = N'C:/Temp/TestDB3_Data.NDF' , SIZE = 1, FILEGROWTH = 10%) TO FILEGROUP UdfGroup

ALTER DATABASE TestDB MODIFY FILEGROUP UdfGroup DEFAULT

GO
18 卸载和装载数据库的完整实例
USE master

CREATE DATABASE MyDb

PRIMARY ( NAME = MyDb1,

FILENAME = 'c:/temp/MyDbdata1.mdf',

SIZE = 10,

MAXSIZE = 200,

FILEGROWTH = 20),

( NAME = MyDb2,

FILENAME = 'c:/temp/MyDbdata2.ndf',

SIZE = 10,

MAXSIZE = 200,

FILEGROWTH = 20),

( NAME = MyDb3,

FILENAME = 'c:/temp/MyDbdata3.ndf',

SIZE = 10,

MAXSIZE = 200,

FILEGROWTH = 20)

LOG ON

( NAME = MyDblog1,

FILENAME = 'c:/temp/MyDblog1.ldf',

SIZE = 10,

MAXSIZE = 200,

FILEGROWTH = 20),

( NAME = MyDblog2,

FILENAME = 'c:/temp/MyDblog2.ldf',

SIZE = 10,

MAXSIZE = 200,

FILEGROWTH = 20)

-- 卸离数据库MyDb

EXEC sp_detach_db 'MyDb','true'

-- 请将数据文件与记录文件转移或复制到d:/temp 后,执行下列命令来附加数据库
EXEC sp_attach_db @dbname = N'MyDb',

@filename1 = N'D:/temp/MyDbdata1.mdf',

@filename2 = N'D:/temp/MyDbdata2.ndf',

@filename3 = N'D:/temp/MyDbdata3.ndf',

@filename4 = N'D:/temp/MyDblog1.ldf',

@filename5 = N'D:/temp/MyDblog2.ldf'

Sp_rename 使用大全
-- 以下的命令语句表示将数据表MyTable 更名为MyCustomer

EXEC sp_rename 'MyTable', 'MyCustomer', 'OBJECT'

-- 以下的命令语句表示将数据表Employees 中的字段BirthDate 更名为Birthday

EXEC sp_rename 'Employees.Birthdate', 'Birthday', 'COLUMN'

-- 以下的命令语句表示将数据表Order Details 中的字段OrderID 更名为OrderNo

EXEC sp_rename '[Order Details].OrderID', 'OrderNo', 'COLUMN'

-- 以下的命令语句表示将数据表Orders 的索引PK_OrderID 更名为Primary_OrderID

EXEC sp_rename 'Orders.PK_OrderID', 'Primary_OrderID', 'INDEX'

-- 以下的命令语句表示将存储过程vrt_vfpman 更名为IdCheckProcedure

EXEC sp_rename 'vrt_vfpman', 'IdCheckProcedure', 'OBJECT'

-- 以下的命令语句表示将规则type_rule 更名为pattern_rule

EXEC sp_rename 'type_rule', 'pattern_rule', 'OBJECT'

-- 以下的命令语句表示将用户自定义数据类型ListType 更名为UsrListType

EXEC sp_rename 'ListType', 'UsrListType', 'USERDATATYPE'

-- 以下的程序代码表示将数据库Test 更名为TestDb

-- 将数据库test 设定成单用户模式
EXEC sp_dboption 'Test', 'single user', 'true'

-- 更名数据库
EXEC sp_rename 'Test', 'TestDb', 'DATABASE'

--将数据库TestDb 恢复成多用户模式
EXEC sp_dboption 'TestDb', 'single user', 'false'
20 关于索引的操作
USE IndexTuneDemo

--执行下列命令为数据表Vfpman建立一个索引：
CREATE NONCLUSTERED

INDEX IX_Name_Vfpman ON Vfpman (name)

WITH PAD_INDEX,FILLFACTOR = 80

-- 更改刚刚建立的索引的键值字段与填充系数，并使其成为一个独一索引
CREATE UNIQUE

INDEX IX_Name_Vfpman ON Vfpman (name,tele,birthday)

WITH PAD_INDEX,FILLFACTOR = 50,DROP_EXISTING

-- 更改索引的名称
EXEC sp_rename 'Vfpman.IX_Name_Vfpman','IX_NTB_Vfpman','INDEX'
21 DBCC命令相关
-- 查看「testc」数据表的数据片段情况
DBCC SHOWCONTIG (testc)

-- 查看「pbcattbl」数据表的pbcattbl_idx 索引的数据片段情况
DBCC SHOWCONTIG (pbcattbl, pbcattbl_idx)

-- 查看「testc」数据表及其所有索引的数据片段情况
DBCC SHOWCONTIG (testc) WITH ALL_INDEXES

-- 显示所有数据表与所有索引的数据片段情况
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

GO
22 执行脚本的例子
DECLARE @DbName nvarchar(20),

@TableName nvarchar(20),

@SQLString nvarchar(500)

SET @DbName = N'TestShen'

SET @TableName = N'testc'

SET @SQLString = N'USE '+@DbName+CHAR(13)

SET @SQLString = @SQLString + N'SELECT * FROM '+@TableName

-- 务必先将命令字符串组合完毕再交给sp_executesql 来执行
EXEC sp_executesql @SQLString

23 sp_executesql的使用方法
DECLARE @IntVariable int;

DECLARE @SQLString nvarchar(500);

DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/

SET @SQLString =

N'SELECT * FROM AdventureWorks.HumanResources.Employee

WHERE ManagerID = @ManagerID';

SET @ParmDefinition = N'@ManagerID tinyint';

/* Execute the string with the first parameter value. */

SET @IntVariable = 197;

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@ManagerID = @IntVariable;

/* Execute the same string with the second parameter value. */

SET @IntVariable = 109;

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@ManagerID = @IntVariable;

24执行一个字符串sql的例子
--char(13) 是回车符 char(10)是换行符 char(9)是制表符
DECLARE @SQLString nvarchar(500)

SET @SQLString = N'DECLARE @MyVariable char(6)'+CHAR(13)

SET @SQLString = @SQLString + N'SET @MyVariable = ''章立民'''+CHAR(13)

SET @SQLString = @SQLString + N'PRINT @MyVariable'

EXEC sp_executesql @SQLString

25 sp_addmessage 和sp_dropmessage实例
USE master;

-- Create a user-defined message in U.S. English

EXEC sp_addmessage

@msgnum = 60000,

@severity = 16,

@msgtext = N'The item named %s already exists in %s.',

@lang = 'us_english';

-- Create a localized version of the same message.

EXEC sp_addmessage

@msgnum = 60000,

@severity = 16,

@msgtext = N'L''élément nomm?%1! existe déj?dans %2!',

@lang = 'French';

-- This statement will fail as long as the localized version

-- of the message exists.

EXEC sp_dropmessage 60000;

-- This statement will drop the message.

EXEC sp_dropmessage

@msgnum = 60000,

@lang = 'all';

26 删除表中的重复数据
USE testshen;

CREATE TABLE myTestFormatFiles (

Col1 smallint,

Col2 nvarchar(50),

Col3 nvarchar(50),

Col4 nvarchar(50)

);

select distinct Col1,Col2,Col3,Col4 into #temp from myTestFormatFiles

--select * from #temp

--select * from myTestFormatFiles

delete from myTestFormatFiles

insert into myTestFormatFiles select * from #temp

drop table #temp
27 查看当前的最大连接数字,服务器名,服务名,版本号
select @@max_connections,@@servername,@@servicename,@@version

28 查看表tb22中CostName为空(不空)的记录数
select count(*) from tb22 where CostName is null

select count(*) from tb22 where CostName is not null

29 查看表tb22中仅CostName为空的记录,以”无数据”显示(显示所有为空记录)

select CostNo,isnull(CostName,'无数据')from tb22 where CostName is null

select CostNo,isnull(CostName,'无数据')from tb22

30 查看表tb22中CostName含有”S”的记录(以SL通配的记录)

select PATINDEX('%S%',CostName) from tb22
select PATINDEX('%SL_%',CostName) from tb22

31 查看表tb22中CostName中含有”S”之前的记录(若像SHIS样的则返回NULL(即不显示))

select * from

(

SELECT CostNo,SUBSTRING(CostName,1,PATINDEX('%S%',CostName) - 1)as CN

FROM tb22

WHERE CostName IS NOT NULL AND PATINDEX('%S%',CostName) > 0

--where t.CN <>''

where t.C2!=''

----------------略微分析下此句的含义返回表达式中,第一次匹配定义的模式的起始位置

32 ① 查看表tb22中CostName中以”S”第一次出现的位置.(显然这个返回值定为1)

select PATINDEX('%S_%',CostName) from tb22
② 查看tb22中CostName中以”SL”第一次出现的位置
select PATINDEX('%SL_%',CostName) from tb22

33 如果在一个已经创建了的表里添加非空的字段,则要用加上可以重复执行建表的语句….

如下
If exists object_id(N’test’,N’U’) is not null

Drop table test

Create table test

(id int,

Name varchar(12),

--以下是自己追加的非空的字段
For_test varchar(24) not null)

34 查看当前数据库下的表的信息
SELECT * FROM INFORMATION_SCHEME.TABLE

--查看数据库里的字段,按照表分类…

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

--查看数据库里的表…..

SELECT * FROM INFORMATION_SCHEMA. Views

35 删除一个表中的字段
Alter table tb22 drop column for_test varchar(12)

--比较,而添加一个字段,则是像如下写法
Alter table tb22 add for_test varchar(12)

36 利用sql生成sql,利用视图INFORMATION_SCHEMA.TABLE_CONSTRAINTS
--产生出禁用外键的脚本
SELECT 'ALTER TABLE '+A.TABLE_NAME+' NOCHECK CONSTRAINT '+ A.CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS A

WHERE

A.CONSTRAINT_CATALOG='testshen'

AND

A.CONSTRAINT_TYPE='FOREIGN KEY'
37 Drop database(表名以”T”和”B”开头并且表中有外键引用关系)的方法
--查找出以”T”和”B”开头的表相关的外键,并执行之
SELECT 'ALTER TABLE '+A.TABLE_NAME+' DROP CONSTRAINT '+ A.CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS A

WHERE

A.CONSTRAINT_TYPE='FOREIGN KEY'

AND

UPPER(SUBSTRING(A.TABLE_NAME,1,2)) IN ('T_','B_')

--查找出以”T”和”B”开头的表的名字,并执行之
SELECT 'DROP TABLE ' + A.TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES A

WHERE

UPPER(SUBSTRING(A.TABLE_NAME,1,2)) IN ('T_','B_')

38 SP_TABLEOPTION和sp_addtype演练
/****** to set the storage cut-off point to 2400 bytes ******/

EXEC SP_TABLEOPTION ‘tblPerson’, 'text in row', 2400

/****** create a zip code datatype ******/

Exec sp_addtype zipcode, 'char(10)', 'NOT NULL', 'dbo'

39 创建表的例子
/****** create a Customer table with a GUID column ******/

CREATE TABLE tblCustomer
(CustomerID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
GivenName Varchar(30) NOT NULL,
FamilyName Varchar(30) NOT NULL,
Address    Varchar(30) NOT NULL,
City       Varchar(30) NOT NULL,
StateCode Char(2)    NOT NULL,
ZipCode    Char(10)    NOT NULL )

40 只看见表的字段…………..而没有数据的写法
SELECT * FROM tb22

WHERE 1 = 0

41 特殊的查询…. 单引号双引号
/****** use pairs of single quotes to denote an apostrophe in a name ******/

SELECT * FROM tblPerson WHERE LastName LIKE 'O''%'

/****** or use double quotes to delimit the string ******/

SELECT * FROM tblPerson WHERE LastName LIKE "O'%"
42 生成临时表,来查询自己的结果,将ename和address组合成一个字段…
select ename+' at '+address as 'Infor',id as 'Iden'

into #tempdog_test

from dog_test

select * from #tempdog_test
43 查看两层的子汇总
/****** show two levels of subtotal ******/

SELECT e.EventName, w.WorkAssignmentDscr, w.PersonID, w.HoursWorked

FROM tblEvent e INNER JOIN tblWorkAssignment w ON e.EventID = w.EventID

ORDER BY EventName, WorkAssignmentDscr, PersonID

COMPUTE SUM(HoursWorked) BY EventName

COMPUTE SUM (HoursWorked)

44 用GROUPING的例子
/****** use the GROUPING aggregate to expose introduced NULLs ******/

SELECT e.EventName, w.WorkAssignmentDscr, w.PersonID,

AVG(HoursWorked) AS "Avg Hours Worked",

SUM(HoursWorked) AS "Total Hours Worked",

GROUPING(PersonID)

FROM tblEvent e INNER JOIN tblWorkAssignment w ON e.EventID = w.EventID

GROUP BY e.EventName, w.WorkAssignmentDscr, w.PersonID

WITH ROLLUP

45 关于Not关键字的些许说明
MySQL数据库系统不支持NOT运算符。在MySQL数据库系统中，NOT运算符只能被用在EXISTS运算符的前面，形如NOT EXISTS。

LIKE运算符也可以与NOT运算符组合使用

Ex:

SELECT TNAME, DNAME,AGE, TSEX

FROM TEACHER

WHERE DNAME NOT LIKE '计算机'

ORDER BY DNAME

Not与Between and的混用
SELECT TNAME, DNAME,AGE, TSEX

FROM TEACHER

WHERE AGE NOT BETWEEN 40 AND 50

ORDER BY AGE

Not可以与其他运算符组合使用，这一点是<>运算符所不能实现的

Not 取反,类似与< >或 !=

SELECT *

FROM TEACHER

WHERE NOT SAL >1500

OREDR BY SAL

NULL值进行取反，结果仍是NULL。

46 关于Like关键字的些许说明

只有CHAR、VARCHAR和TEXT类型的数据才能使用LIKE运算符和通配符。

Like 之应用

--Ex:查看以'重庆'开头'3'结尾的CostName和address的记录
select CostName,address from tb22

where address like '重庆%3'

47 关于组合查询的些许说明

组合通配符及其匹配条件

查询条件
匹配条件

LIKE '5[%]'
5%

LIKE '[_]n'
_n

LIKE '[a-cdf]'
a，b， c，d或f

LIKE '[-acdf]'
-， a， c， d 或 f

LIKE '[ [ ]'
[

LIKE ']'
]

LIKE 'abc[_]d%'
abc_d 或 abc_d……

LIKE 'abc[def]'
abcd， abce，或abcf

48 用update 来更新关联表…

UPDATE T_StoreFront_S

SET

CustArea_ID = B.CustArea_ID

FROM

T_StoreFront_S A

Join

T_B_CUSTAREA B

On A.CustArea_Name = B.CustArea_Name

49 用DBCC强制改变当前标识值
--报告当前表中的标志值
USE AdventureWorks;

DBCC CHECKIDENT ('HumanResources.Employee', NORESEED)

--强制使当前的标志值设置为30

USE AdventureWorks;

DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)

50 查看当前实例下的数据库名
select * from sys.sysdatabases

51 查看给定字段对应的表名（这里限制了不是系统表）
DECLARE @CNAME VARCHAR(50)

SET @CNAME='JNO'

SELECT DISTINCT TB.NAME FROM sys.sysColumns AS CN INNER JOIN sys.sysobjects AS TB ON CN.ID=TB.ID WHERE CN.NAME=@CNAME

and TB.type<>'S'

52 Sql Server进程管理
select spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses where db_name(dbid)='SPM_II'

kill 72

drop database SPM_II

上一篇：Sql Server 2005 哈希索引的实现

下一篇：[视频]第1章 SQL Server 2008入门知识 1.SQL SERVER 2008简介