Common SQL System sentence

-- Constraints

Alter table doc_exd with nocheck
Add constraint exd_check check (column_a> 1)

-- Disable Constraints

Alter table B _base nocheck constraint B _base_check

-- Enable constraints

With check Constraint

-- View Constraints

Sp_helpconstraint B _enterbase

--- Reduce logs

Dump transaction tb_hongdatabase with no_log
Backup log tb_hongdatabase with no_log
DBCC shrinkdatabase (tb_hongdatabase)
Exec sp_dboption 'tb _ hongdatabase', 'autowrite', 'true'

--- Clear duplicate records

Delete table where Sn not in (select Min (SN) from Table group by col1, col2)

--- Change the table owner

Exec sp_changeobjectowner 'hongkang. t_sellplan', 'dbo'

--- Trigger

Create trigger tri_a on
For insert, update, delete
As
If exists (select * From deleted)
Begin
Delete from B where... -- (all columns have the same conditions as the deleted table)
End
If exists (select * From inserted)
Begin
Insert into B select * From inserted
End

 

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 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 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

Fixed 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 linked servers

Sp_helplinkedsrvlogin

View remote database user logon information

Sp_helpremotelogin

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

Sp_spaceused @ objname

Sp_msforeachtable 'exec sp_spaceused "? "'

You can also use the sp_toptables process to view the maximum n tables (50 by default ).

View the index information of a data object in a database

Sp_helpindex @ objname

You can also use the sp_nchelpindex process to view more detailed indexes.
Sp_nchelpindex @ objname

Clustered indexes sort records in physical order and occupy less space.
We recommend that you use non-clustered indexes and constraints for tables with frequent key value DML operations. The fillfactor parameter uses the default value.
View the constraints of a data object in a database
Sp_helpconstraint @ objname

9. view all stored procedures and functions in the database

Use @ database_name
Sp_stored_procedures
View the source code of stored procedures and functions
Sp_helptext ''@ procedure_name''

View the Data Object Name containing a string @ Str

Select distinct object_name (ID) from syscomments where text like ''% @ STR %''

Create an encrypted stored procedure or function with the with encryption parameter before the

You can use sp_decrypt to decrypt encrypted stored procedures and functions.

10. View user and process information in the database

Sp_who
View information about active users and processes in the SQL Server database
Sp_who ''active''
View the locks in the SQL Server database
Sp_lock

Process No. 1--50 is used internally in the SQL server system. If the process no. is greater than 50, it is the user's connection process.
Spid is the process number, dbid is the database number, and objid is the data object number
View the SQL statement being executed by the Process
DBCC inputbuffer ()

We recommend that you use the improved sp_who3 process to directly view the SQL statements run by the process.
Sp_who3

Check the process of using sp_who_lock for deadlocks

Sp_who_lock

11. Methods for shrinking Database Log Files

Shrink database logs in simple recovery mode. The size unit of @ database_name_log after the contraction is M.
Backup log @ database_name with no_log
DBCC shrinkfile (@ database_name_log, 5)
12. How to analyze SQL Server SQL statements:

Set statistics time {on | off}
Set statistics Io {on | off}
Display query execution plan in graphical mode

In the query analyzer-> query-> display estimated evaluation plan (d)-Ctrl-l or click the graph in the toolbar

Display query execution plan in text mode
Set showplan_all {on | off}

Set showplan_text {on | off}
Set statistics profile {on | off}

13. When an inconsistency error occurs, the NT Event Viewer displays error 3624. How to fix the database

Comment out the table referenced in the application with an inconsistent error, recover the table on the backup or other machines, and then perform the repair operation.

Alter database [@ error_database_name] Set single_user

Fix inconsistent tables

DBCC checktable (''@ error_table_name'', repair_allow_data_loss)

Or, unfortunately, you can fix the name of a small database with an inconsistent error.

DBCC checkdb (''@ error_database_name'', repair_allow_data_loss)
Alter database [@ error_database_name] Set multi_user
Checkdb has three parameters:
Repair_allow_data_loss includes allocating and unassigning rows and pages to correct the allocation error, structure row, or page error,
And delete corrupted text objects. These repairs may cause data loss.
The repair operation can be completed under the user transaction to allow the user to roll back the changes.
If the rollback is fixed, the database will still contain errors and the backup should be restored.
If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix.
After the restoration is completed, back up the database.
Repair_fast Performs small and time-consuming repair operations, such as fixing additional keys in non-clustered indexes.
These repairs can be completed quickly without the risk of data loss.
Repair_rebuild performs all repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing ).
There is no risk of data loss when performing these repairs.

Transactions

Create procedure
@ I int
As
Begin
Set xact_abort on
Begin tran
Update AA set col1 = col1-@ I where id = 1
Update AA set col1 = col1 + @ I where id = 2
Commit tran
End

Go

Begin
Update AA set col1 = col1-@ I where id = 1
If @ error> 0
Begin
Rollback tran
Return
End
Update AA set col1 = col1 + @ I where id = 2
If @ error> 0
Rollback tran
Else
Commit tran

Clear Database

-- Disable all foreign key constraints first
Exec sp_msforeachtable "alter table? Nocheck constraint all"

-- Disable all triggers
Exec sp_msforeachtable "alter table? Disable trigger all"

-- Then delete the data
/* Exec sp_msforeachtable @ command1 = 'truncate table? '
, @ Whereand = 'and name not like ''' sys %''
And name not in (''dtproperties '', ''special Table 2'', ''special Table 3 '')'*/
Exec sp_msforeachtable @ command1 = 'truncate table? ',
@ Whereand = 'and objectproperty (O. ID, ''tablehasforeignref '') = 0'
Exec sp_msforeachtable @ command1 = 'delete from? ',
@ Whereand = 'and objectproperty (O. ID, ''tablehasforeignref '') = 1'
-- Enable all foreign key constraints
Exec sp_msforeachtable "alter table? Check constraint all"

-- Enable all triggers
Exec sp_msforeachtable "alter table? Enable trigger all"

 

Compressed Database

-- 1. Clear logs
Dump transaction database with no_log

-- 2. truncate transaction logs
Backup log database with no_log

-- 3. Shrink database files
DBCC shrinkdatabase (database)

-- 4. Set automatic contraction
Alter database set auto_shrink on

Current connection data of the database

Select count (1) from Master .. sysprocesses where status <> 'background'

SQL Export

Exec master .. xp_mongoshell 'bcp "select * from database name. DBO. Table Name" queryout "C:/xxx.xls"-C-S server-U sa-P password'

Query another database

Select * From OpenRowSet ('sqloledb', 'Another SQL server name or IP address '; 'username'; 'Password', database name to be queried. DBO. Name of the table to be queried)

Cross tabulation instance

Table creation:

Run in the query Analyzer:

Create Table [test] (

[ID] [int] identity (1, 1) not null,

[Name] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Subject] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Source] [numeric] (18, 0) null

) On [primary]

Go

Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', n '', 60)

Insert into [test] ([name], [subject], [Source]) values (N 'Li si', N 'mat', 70)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 80)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', N 'mat', 75)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 57)

Insert into [test] ([name], [subject], [Source]) values (n'li si', n' ', 80)

Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', N 'angles', 100)

Go

 

Implementation of cross tabulation statements:

-- Used: the number of columns in the crosstab chart is determined.

Select name, sum (case subject when 'mate' then source else 0 end) as 'mate ',

Sum (case subject when 'then source else 0 end) as 'hangzhou ',

Sum (case subject when 'chine' then source else 0 end) as 'chine'

From Test

Group by name

 

 

-- Used: the number of columns in the crosstab chart is uncertain.

Declare @ SQL varchar (8000)

Set @ SQL = 'select name ,'

 

Select @ SQL = @ SQL + 'sum (case subject when ''' + Subject + '''

Then source else 0 end) as ''' + Subject + ''','

From (select distinct subject from test) as

 

Select @ SQL = left (@ SQL, Len (@ SQL)-1) + 'from test group by name'

Exec (@ SQL)

Go

Export table structure

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' -- this condition is added if only the specified table is queried.
Order by A. ID, A. colorder

Generate random number
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [randtb] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [randtb]
Go
Create Table [DBO]. [randtb] (randno varchar (50) primary key)
Go
Declare
@ NO char (20 ),
@ Num int,
@ Stime datetime,
@ Etime datetime
Set nocount on
Set @ stime = getdate ()
Set @ num = 0
While @ num <50000
Begin
-- Set @ No = right (cast (RAND () * 10000000000000000) as bigint), 15) -- number
Set @ No = right (replace (newid (), '-', ''), 15) -- Letter
If not exists (select 1 from randtb where randno = @ No)
Begin
Insert into randtb values (@ No)
Set @ num = @ num + 1
End
End
Set @ etime = getdate ()
Set nocount off
Print datediff (MS, @ stime, @ etime)
Drop table [DBO]. [randtb]