Summary of frequently-used database operations and database management Statements page 1/2

Source: Internet
Author: User
I would like to thank the author for his hard work and many statements frequently used in database operations and database management.

I would like to thank the author for his hard work and many statements frequently used in database operations and database management.

/*
-- Organizer: Eternal de shadow

-- Sorting time: 2010/06/08

-- Content: SQL function introduction:
*/

--★★SQL2000 queries the attributes 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 No. = a. colorder,
Field name = a. name,
Id = case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' end,
Primary Key = case when exists (SELECT 1 FROM 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 '),
Decimal places = isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0 ),
Allow null = case when a. isnullable = 1 then '√ 'else' 'end,
Default Value = isnull (e. text ,''),
Field description = isnull (g. [value], '')
FROM
Syscolumns
Left join
Policypes 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 be query' -- if only the specified table is queried, add this condition.
Order
A. id, a. colorder


--★★SQL2005 queries the attributes of each (a) Table field:★★★★★★★★★★★★★★★★★★
-- ===================================================== ======================================
-- Query table structure information
-- Producer build 2005.08 (reference please keep this information)
-- ===================================================== ======================================
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 corresponding to the field description (multiple descriptions of different names can be added for one field)
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 '-- name of the description corresponding to the table description (multiple descriptions with different names can be added to a table)
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, 'isdesending ')
WHEN 1 THEN 'desc' WHEN 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 -- if a column contains multiple indexes, only 1st indexes are displayed.
(
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' table to be queried '-- this condition is added if only the specified table is queried.
Order by o. name, C. column_id

--★★SQL2005 index and primary key information:★★★★★★★★★★★★★★★★★★
-- ===================================================== ======================================
-- Index and primary key information
-- Producer build 2005.08 (reference please keep this information)
-- ===================================================== ======================================
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, 'isdesending ')
WHEN 1 THEN 'desc' WHEN 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 supports large memory capacity:★★★★★★★★★★★★★★★★★★

/*
32-bit operating systems have a major defect. applications cannot access process address spaces larger than 4 GB, because 32-bit pointers cannot save address spaces larger than 4 GB.
If it is larger than 4 GB, you need to use the address window extension (AWE), the specific operation is as follows:
1. Start physical address extension
(1) locate C: \ boot. ini and delete its read-only attribute.
(2) edit boot. ini and add the/PAE parameter to the ARC path. For example:
In windows Server 2003 Enterprise Edition, the edited ARC path is as follows:
Muti (0) disk (0) partition (1) windows = "windows Server 2003 Enterprise, Edition"/fastdetect/PAE
Save and restore it to read-only mode, and then restart the computer.

If the available physical memory on the computer exceeds 16 GB, ensure that the/3 GB parameter is not in the boot. ini file.
*/

--★★How to enable AWE options:★★★★★★★★★★★★★★★★★★

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 '-- Minimum server memory
Sp_configure 'max server memory '-- maximum server memory
Sp_configure 'index create memory '-- memory occupied by index creation
Sp_configure 'min memory per query' -- Minimum memory occupied by each query


--★★Obtain disk read/write status:★★★★★★★★★★★★★★★★★★

Select
@ Total_read as 'number of disk reads ',
@ Total_write as 'number of disk writes ',
@ Total_error as 'number of disk write errors ',
Getdate () as 'current time'

--★★Obtain the I/O statistics of database files:★★★★★★★★★★★★★★★★★★

Select * from fn_virtualfilestats (null, null)

--★★Obtain I/O status:★★★★★★★★★★★★★★★★★★

Select
@ Id_busy, -- the time when SQL is used to execute input and output operations since the last time it was started
@ Timeticks: the number of microseconds corresponding to each clock cycle
@ Id_busy * @ timeticks as 'I/O operation milliseconds ',
Getdate () as 'current time'

--★★Check the CPU activity of the SQL Server:★★★★★★★★★★★★★★★★★★

Select
@ Cpu_busy, -- working time since the last startup
@ Timeticks: the number of microseconds corresponding to each clock cycle
@ Cpu_busy * cast (@ timeticks as float)/1000 as 'cpu working time (seconds )',
@ Idie * cast (@ timeticks as float)/1000 as 'cpu idle time (seconds )'
Getdate () as 'current time'

--★★Obtain network packet statistics:★★★★★★★★★★★★★★★★★★

Select
Getdate () as 'current time ',
@ Pack_received as 'number of input packets ',
@ Pack_sent as 'number of output packets ',
@ Packet_error as 'number of error packages'


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

Create function fgetsstatus (
@ Servername varchar (50) -- server name
, @ Userid varchar (50) = 'sa '-- user name. If it is an nt authentication method, it is null.
, @ Password varchar (50) = ''-- password

) Returns varchar (20)
As
Begin
Declare @ re varchar (20), @ ire int -- Return status
Declare @ srvid int -- defines the server and database Set id
Declare @ err int, @ src varchar (255), @ desc varchar (255) -- error handling variable

--★★Create an sqldmo object:★★★★★★★★★★★★★★★★★★

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

--★★Connection Server:★★★★★★★★★★★★★★★★★★

If isnull (@ userid, '') ='' -- if it is an 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 'starting ...'
When 5 then 'starting and stopping ...'
When 6 then 'Connection ...'
When 7 then 'paused... 'end
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 code: '+ @ 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 ),
Index space varchar (10 ),
Unused space varchar (10 ))
Exec sp_MSForEachTable @ command1 = n' insert tb exec sp_spaceused ''? '''

Select * from tb

--★★View the server version:★★★★★★★★★★★★★★★★★★

SELECT
SERVERPROPERTY ('productversion '),
SERVERPROPERTY ('productlevel '),
SERVERPROPERTY ('version ')

--★★View the database offline time:★★★★★★★★★★★★★★★★★★

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
Go
EXEC sp_configure 'xp _ Your shell', 1
RECONFIGURE
GO
Select a. name, a. database_id, a. create_date, B. physical_name into #
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_mongoshell 'dir' + physical_name + ''' + char (13) + char (10) from #
Execute (@ string)

Select a. name, substring (B .info, 0, 20) as offline time, a. database_id, a. create_date, a. physical_name
From # a left join # 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 _ Your shell', 0
RECONFIGURE
Go
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
Go

/*

1. view the database version

Select @ version

2. view the operating system parameters of the machine where the database is located.

Exec master... xp_msver ..

3. view the database startup parameters!

Sp_configure
4. view the database startup time.

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

View the database server name and Instance name.

Print ''server Name ......: ''+ convert (varchar (30), @ SERVERNAME ).

Print ''instance ......: ''+ convert (varchar (30), @ SERVICENAME )...

5. View All Database names and sizes

Sp_helpdb.

SQL statement used to rename a database

Sp_renamedb ''old _ dbname'', ''new _ dbname''

6. View logon information of all database users ..

Sp_helplogins

View the role information of all database users

Sp_helpsrvrolemember!

Fix the fix_orphan_user script or LoneUser process that can be used to isolate users during server migration.

Change the user owner of a Data Object

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

Note: changing any part of the object name may corrupt the script and stored procedure.

You can use the add_login_to_aserver script to back up the database user logon information on a server.

7. view the linked server...

Sp_helplinkedsrvlogin

View the logon information of a remote database user.

Sp_helpremotelogin ..

8. view the size of a data object in a database!

Sp_spaceused @ objname

You can also use the sp_toptables process to check the maximum N value (the default value 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 (10) + '-' + 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 10 and 20 -- repl

--★★Query all the indexes of table tb in the database:★★★★★★★★★★★★★★★★★★

Use db
Go
Select
A. index_id, --- Index Number
B. name, --- index name
Avg_fragmentation_in_percent --- logical fragmentation of the Index
From
Sys. dm_db_indx_physical_stats (db_id (), object_id (N 'create. consume '), null, null) as
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 on the jobs table
Grant all on jobs TO r_test
-- Grant the role r_test the SELECT permission on the titles table.
Grant select on titles TO r_test

-- Add login l_test, set password to pwd, default database to pubs
EXEC sp_addlogin 'l _ test', 'pwd', 'pubs'

-- Add the security account u_test to the database pubs for login l_test
EXEC sp_grantdbaccess 'l _ test', 'U _ Test'

-- Add u_test as a member of role r_test
EXEC sp_addrolemember 'r _ test', 'U _ Test'

-- Deny the SELECT permission of the security account u_test on the titles table
Deny select on titles TO u_test

/* -- After completing the preceding steps, log on with l_test to perform all operations on the jobs table, but cannot query the titles table. Although the role r_test has the select permission on the titles table, however, the select permission for titles has been explicitly denied in the security account, so l_test has no select permission for the titles table --*/

-- Delete a 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'

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.