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

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'