Summary of statements for operations and management databases that are frequently used in the database 1th/2 page

/*
--Finishing: The Eternal de shadow

--Finishing Time: 2010/06/08

-Content: Introduction to SQL Functions:
*/

--★★sql2000 query out the properties of each (a) table field: ★★★★★★★★★★★★★★★★★★

SELECT
Table name = case when a.colorder=1 then d.name else ' "End,
Table Description = case when a.colorder=1 then IsNull (F.value, ' ") Else ' end,
Field ordinal = A.colorder,
Field name = A.name,
identity = case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else ' end,
Primary KEY = case when exists (SELECT 1 to sysobjects where xtype= ' PK ' and Parent_obj=a.id and name in (
SELECT name from sysindexes WHERE indid in (
SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid)) Then ' √ ' Else ' end,
Type = B.name,
Bytes occupied = a.length,
Length = ColumnProperty (a.id,a.name, ' PRECISION '),
Number of decimal places = IsNull (ColumnProperty (a.id,a.name, ' Scale '), 0),
Allow NULL = case time a.isnullable=1 Then ' √ ' Else ' end,
Default = IsNull (E.text, ""),
Field Description = IsNull (G.[value], "")
From
Syscolumns A
Left Join
Systypes b
On
A.xusertype=b.xusertype
INNER JOIN
sysobjects D
On
A.id=d.id and D.xtype= ' U ' and d.name<> ' dtproperties '
Left Join
syscomments E
On
A.cdefault=e.id
Left Join
Sysproperties g
On
A.id=g.id and A.colid=g.smallid
Left Join
Sysproperties F
On
D.id=f.id and F.smallid=0
where
D.name= ' table to query '--if only the specified table is queried, plus this condition
ORDER BY
A.id,a.colorder


--★★sql2005 query out the properties of each (a) table field: ★★★★★★★★★★★★★★★★★★
-- ========================================================================
--Table Structure information query
--Jiangjian 2005.08 (please keep this information for reference)
-- ========================================================================
SELECT
Tablename=case when c.column_id=1 THEN o.name ELSE N ' "End,
Tabledesc=isnull (case when c.column_id=1 THEN ptb.[ Value], End,n '),
COLUMN_ID=C.COLUMN_ID,
Columnname=c.name,
Primarykey=isnull (IDX. Primarykey,n '),
[Identity]=case when C.is_identity=1 THEN n ' √ ' ELSE n ' "End,
Computed=case when c.is_computed=1 THEN n ' √ ' ELSE n ' "End,
Type=t.name,
Length=c.max_length,
Precision=c.precision,
Scale=c.scale,
Nullable=case when c.is_nullable=1 THEN n ' √ ' ELSE n ' "End,
[Default]=isnull (D.definition,n '),
Columndesc=isnull (Pfd.[value],n '),
Indexname=isnull (IDX. Indexname,n '),
Indexsort=isnull (IDX. Sort,n '),
Create_date=o.create_date,
Modify_date=o.modify_date
From Sys.columns C
INNER JOIN sys.objects O
On c.[object_id]=o.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
INNER JOIN sys.types T
On c.user_type_id=t.user_type_id
Left JOIN sys.default_constraints D
On c.[object_id]=d.parent_object_id
and c.column_id=d.parent_column_id
and c.default_object_id=d.[object_id]
Left JOIN sys.extended_properties PFD
On Pfd.class=1
and c.[object_id]=pfd.major_id
and c.column_id=pfd.minor_id
--and Pfd.name= ' Caption '--The description name for the field description (one field can add more than one description of different name)
Left JOIN sys.extended_properties PTB
On Ptb.class=1
and Ptb.minor_id=0
and c.[object_id]=ptb.major_id
--and Pfd.name= ' Caption '--table description of the corresponding description name (a table can add more than one description of different name)
Left JOIN--index and primary key information
(
SELECT
Idxc. [OBJECT_ID],
IDXC.COLUMN_ID,
Sort=case Indexkey_property (idxc.[ object_id],idxc.index_id,idxc.index_column_id, ' isdescending ')
When 1 THEN ' DESC ' if 0 THEN ' ASC ' ELSE ' end,
Primarykey=case when Idx.is_primary_key=1 THEN n ' √ ' ELSE n ' "End,
Indexname=idx. Name
From Sys.indexes IDX
INNER JOIN sys.index_columns IDXC
On IDX. [OBJECT_ID]=IDXC. [OBJECT_ID]
and idx.index_id=idxc.index_id
Left JOIN sys.key_constraints KC
On IDX. [OBJECT_ID]=KC.[PARENT_OBJECT_ID]
and idx.index_id=kc.unique_index_id
INNER JOIN--Displays only the 1th index information for a column that contains multiple indexes
(
SELECT [object_id], column_id, Index_id=min (index_id)
From Sys.index_columns
GROUP by [object_id], column_id
) Idxcuq
On IDXC. [Object_id]=idxcuq. [OBJECT_ID]
and IDXC. Column_id=idxcuq. column_id
and idxc.index_id=idxcuq.index_id
) IDX
On C.[object_id]=idx. [OBJECT_ID]
and c.column_id=idx.column_id
--where o.name=n ' tables to query '--if only the specified table is queried, plus this condition
ORDER BY o.name,c.column_id

--★★SQL2005 Index and PRIMARY key information: ★★★★★★★★★★★★★★★★★★
-- ========================================================================
--Index and primary key information
--Jiangjian 2005.08 (please keep this information for reference)
-- ========================================================================
SELECT
TABLEID=O.[OBJECT_ID],
Tablename=o.name,
Indexid=isnull (kc.[object_id],idx.index_id),
Indexname=idx. Name,
Indextype=isnull (Kc.type_desc, ' Index '),
INDEX_COLUMN_ID=IDXC.INDEX_COLUMN_ID,
COLUMNID=C.COLUMN_ID,
Columnname=c.name,
Sort=case Indexkey_property (idxc.[ object_id],idxc.index_id,idxc.index_column_id, ' isdescending ')
When 1 THEN ' DESC ' if 0 THEN ' ASC ' ELSE ' end,
Primarykey=case when Idx.is_primary_key=1 THEN n ' √ ' ELSE n ' "End,
[Uqique]=case when idx.is_unique=1 THEN n ' √ ' ELSE n ' "End,
Ignore_dup_key=case when Idx.ignore_dup_key=1 THEN n ' √ ' ELSE n ' "End,
Disabled=case when idx.is_disabled=1 THEN n ' √ ' ELSE n ' "End,
Fill_factor=idx.fill_factor,
Padded=case when idx.is_padded=1 THEN n ' √ ' ELSE n ' "End
From Sys.indexes IDX
INNER JOIN sys.index_columns IDXC
On IDX. [OBJECT_ID]=IDXC. [OBJECT_ID]
and idx.index_id=idxc.index_id
Left JOIN sys.key_constraints KC
On IDX. [OBJECT_ID]=KC.[PARENT_OBJECT_ID]
and idx.index_id=kc.unique_index_id
INNER JOIN sys.objects O
On O.[object_id]=idx. [OBJECT_ID]
INNER JOIN sys.columns C
On o.[object_id]=c.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
and IDXC. column_id=c.column_id


--★★sql server support for large-capacity memory: ★★★★★★★★★★★★★★★★★★

/*
The 32-bit operating system has a large flaw that the application cannot access the process address space greater than 4G because 32-bit pointers cannot save the address space greater than 4G
If it is greater than 4G, you need to use an Address windowing extension (AWE), as follows:
1, Start Physical Address Extension
(1) Locate the C:\boot.ini and delete its read-only property.
(2) Edit Boot.ini and add/PAE parameters to the ARC path. For example:
In Windows Server 2003 Enterprise Edition, the following ARC path is edited:
Muti (0) disk (0) partition (1) windows= "Windows Server 2003 Enterprise,edition"/fastdetect/pae
Restore it to read-only mode after saving, and then restart the computer.

If you have more than 16G of physical memory available on your computer, make sure that there are no/3GB parameters in the Boot.ini file
*/

--★★ How to start the AWE option: ★★★★★★★★★★★★★★★★★★

sp_configure ' show advanced options ', 1
Reconfigure
Go
Sp_configue ' awe enabled ', 1
Reconfigure
Go

--★★ Manually configure memory options: ★★★★★★★★★★★★★★★★★★

sp_configure ' show advanced options ', 1
Go
Reconfigure
Go
sp_configure ' min server memory '-Server minimum memory
sp_configure ' max server memory '-Server Max memory
sp_configure ' index create memory '--Create an index-occupied memory
sp_configure ' min memory per query '--minimum memory consumption


--★★ get disk Read and write: ★★★★★★★★★★★★★★★★★★

Select
@ @total_read as ' Read disk count ',
@ @total_write as ' number of writes to disk ',
@ @total_error as ' disk write errors ',
GETDATE () as ' current time '

--★★ get I/O statistics for the database file: ★★★★★★★★★★★★★★★★★★

SELECT * from Fn_virtualfilestats (null,null)

--★★ get I/o work: ★★★★★★★★★★★★★★★★★★

Select
@ @id_busy,--sql time to perform input and output operations since the last boot
@ @timeticks,--the number of microseconds per clock cycle
@ @id_busy *@ @timeticks as ' I/O operation milliseconds ',
GETDATE () as ' current time '

--★★ View SQL SEVER CPU activity, working: ★★★★★★★★★★★★★★★★★★

Select
@ @cpu_busy,--working hours since the last boot
@ @timeticks,--the number of microseconds per clock cycle
@ @cpu_busy *cast (@ @timeticks as float)/1000 as ' CPU working time (SEC) ',
@ @idie *cast (@ @timeticks as float)/1000 as ' CPU idle time (seconds) '
GETDATE () as ' current time '

--★★ Get network packet statistics: ★★★★★★★★★★★★★★★★★★

Select
GETDATE () as ' current time ',
@ @pack_received as ' input packet quantity ',
@ @pack_sent as ' output packet quantity ',
@ @packet_error as ' Error pack number '


--★★ View server working status: ★★★★★★★★★★★★★★★★★★

Create Function Fgetsstatus (
@servername varchar (50)--server name
, @userid varchar = ' sa '-user name, NULL if NT authentication method
, @password varchar (50) = '--Password

) returns varchar (20)
As
Begin
DECLARE @re varchar, @ire INT--Return status
DECLARE @srvid INT--Define server, database set ID
declare @err int, @src varchar (255), @desc varchar (255)--Error handling variable

--★★ Create SQLDMO object: ★★★★★★★★★★★★★★★★★★

exec @err =sp_oacreate ' sqldmo.sqlserver ', @srvid output
If @err <>0 goto Lberr

--★★ Connection server: ★★★★★★★★★★★★★★★★★★

If IsNull (@userid, ') = '--If the NT authentication method
Begin
EXEC @err =sp_oasetproperty @srvid, ' LoginSecure ', 1
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @srvid, ' Connect ', NULL, @servername
End
Else
EXEC @err =sp_oamethod @srvid, ' Connect ', NULL, @servername, @userid, @password

If @err <>0 goto Lberr

--★★ Get Server Status: ★★★★★★★★★★★★★★★★★★

EXEC @err =sp_oagetproperty @srvid, ' Status ', @ire output
If @err <>0 goto Lberr

Set @re =case @ire when 0 Then ' unknown '
When 1 Then ' run ... '
When 2 Then ' paused '
When 3 Then ' stop ... '
When 4 Then ' is starting ... '
When 5 Then ' is starting stop ... '
When 6 Then ' Connect ... '
When 7 Then ' is pausing ... '
Return (@re)
Lberr:
EXEC sp_OAGetErrorInfo NULL, @src out, @desc out
DECLARE @errb varbinary (4)
Set @errb =cast (@err as varbinary (4))
EXEC master.. Xp_varbintohexstr @errb, @re out
Set @re = ' ERROR number: ' + @re
+char (13) + ' ERROR Source: ' + @src
+char (13) + ' ERROR description: ' + @desc
Return (@re)
End
Go

Select Dbo.fgetsstatus (' 192.168.102.208 ', ' sa ', ' sa ')

--------------------
--run ...

--★★ Get Server Status: ★★★★★★★★★★★★★★★★★★

If object_id (' TB ') is not null drop table TB
Go
CREATE TABLE TB (
table name sysname,
Number of records int,
Reserved space nvarchar (10),
Use space varchar (10),
The index uses space varchar (10),
Unused space varchar (10))
exec sp_msforeachtable @command1 =n ' Insert TB exec sp_spaceused '? '

SELECT * FROM TB

--★★ View Server version: ★★★★★★★★★★★★★★★★★★

SELECT
Serverproperty (' ProductVersion '),
Serverproperty (' ProductLevel '),
Serverproperty (' edition ')

--★★ View Database offline time: ★★★★★★★★★★★★★★★★★★

EXEC sp_configure ' show advanced options ', 1
Reconfigure
Go
EXEC sp_configure ' xp_cmdshell ', 1
Reconfigure
Go
Select A.name,a.database_id,a.create_date,b.physical_name into #a
From sys.databases a LEFT join Sys.master_files B on
a.database_id=b.database_id where Has_dbaccess (a.name) <>1 and b.type=1

CREATE TABLE #b (info varchar (500))
DECLARE @string varchar (max)
Set @string = '
Select @string = @string + ' INSERT into #b exec xp_cmdshell ' dir ' + physical_name + ' ' +char +char (a) from #a
Execute (@string)

Select A.name,substring (b.info,0,20) as offline time, A.database_id,a.create_date,a.physical_name
From #a a LEFT join #b B on
REVERSE (substring (REVERSE (physical_name), 0,charindex (' \ ', REVERSE (physical_name)))
=reverse (substring (REVERSE (info), 0,charindex (", REVERSE (Info))")

drop table #a, #b
Go
EXEC sp_configure ' xp_cmdshell ', 0
Reconfigure
Go
EXEC sp_configure ' show advanced options ', 0
Reconfigure
Go

/*

1. View the version of the database

SELECT @ @version

2. View the machine operating system parameters of the database.

EXEC master.. Xp_msver..

3. View the database startup Parameters!

sp_configure
4. View database startup time.

Select CONVERT (varchar, login_time,120) from master. sysprocesses where spid=1

View the database server name and instance name.

print ' Server Name ... ...: ' + CONVERT (varchar (), @ @SERVERNAME). \ \ \ \

print ' Instance .... ...: ' + CONVERT (varchar, @ @SERVICENAME) ... and so on. "----

5. View all database names and sizes

sp_helpdb.

To rename SQL for a database

Sp_renamedb ' Old_dbname ', ' new_dbname '

6. View all database user login information.

Sp_helplogins

View the role information that all database users belong to

Sp_helpsrvrolemember!

A fix_orphan_user script or loneuser procedure that can be used when repairing orphaned users when migrating servers.

Change the user owner of a data object

sp_changeobjectowner [@objectname =] ' object ', [@newowner =] ' owner '.

Note: Changing any part of an object name can corrupt scripts and stored procedures.

To back up the database user login information on a single server, you can use Add_login_to_aserver script

7. View Linked server ...

Sp_helplinkedsrvlogin

View Remote database user logon information.

Sp_helpremotelogin..

8. View the size of a data object under a database!

sp_spaceused @objname

You can also use the Sp_toptables procedure to see the largest n (default is 50)

*/

--★★ View Job Execution: ★★★★★★★★★★★★★★★★★★

Select category = Jc.name,
category_id = jc.category_id,
Job_name = J.name,
job_enabled = j.enabled,
Last_run_time = cast (js.last_run_date as varchar) + '-' + cast (js.last_run_time as varchar (10)),
Last_run_duration = Js.last_run_duration,
Last_run_status = Js.last_run_outcome,
Last_run_msg = Js.last_outcome_message + cast (Nullif (js.last_run_outcome,1) as varchar (2)),
job_created = j.date_created,
Job_modified = j.date_modified
From Msdb.dbo.sysjobs J
INNER JOIN Msdb.dbo.sysjobservers JS
On j.job_id = js.job_id
INNER JOIN Msdb.dbo.syscategories JC
On j.category_id = jc.category_id
where j.enabled = 1
and Js.last_run_outcome in (0,1,3,5)--0:fail 1:succ 3:cancel 5:first Run
and jc.category_id not between-repl

--★★ query for all indexes in table TB in database db: ★★★★★★★★★★★★★★★★★★

Use DB
Go
Select
A.INDEX_ID,---Index number
B.name,---Index Name
Logical fragmentation of the avg_fragmentation_in_percent---index
From
Sys.dm_db_indx_physical_stats (db_id (), object_id (N ' Create.consume '), Null,null,null) as a
Join
Sys.indexes as B
On
a.object_id=b.object_id
and
a.index_id=b.index_id
Go

--★★ User member Permissions: ★★★★★★★★★★★★★★★★★★

Use pubs

--Create Role R_test
EXEC sp_addrole ' R_test '

--Grant R_test all permissions to the jobs table
GRANT all on jobs to R_test
--Grant role r_test SELECT permissions on the titles table
GRANT SELECT on titles to R_test

--Add login l_test, set password to PWD, default database is pubs
EXEC sp_addlogin ' l_test ', ' pwd ', ' pubs '

--Add a security account for the login l_test in the database pubs u_test
EXEC sp_grantdbaccess ' l_test ', ' u_test '

--Add u_test as a member of the role R_test
EXEC sp_addrolemember ' r_test ', ' u_test '

--Deny security account u_test SELECT permissions on the titles table
DENY SELECT on titles to U_test

/*--after completing the above steps, log in with L_test, you can do all the work on the jobs table, but you cannot query the titles table, although the role r_test has the SELECT permission of the titles table, but has explicitly rejected the SELECT permission for titles in the security account. So l_test SELECT permissions without a titles table--*/

--Remove the security account from the database pubs
EXEC sp_revokedbaccess ' U_test '

--Delete Login l_test
EXEC sp_droplogin ' L_test '

--Delete Role R_test
EXEC sp_droprole ' R_test '