數據字典總結(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簡介