Common SQL and T-SQL statements for SQL Server database management

1. sort by strokes of the Last Name:
Select * From tablename order by customername collate chinese_prc_stroke_ci_as

2. database encryption:
Select encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different

3. Retrieve the fields in the table:
Declare @ list varchar (1000), @ SQL nvarchar (1000)
Select @ list = @ list + ',' + B. name from sysobjects A, syscolumns B where a. ID = B. ID and A. Name = 'table'
Set @ SQL = 'select' + right (@ list, Len (@ list)-1) + 'from table'
Exec (@ SQL)

4. View hard disk partitions:
Exec master .. xp_fixeddrives

5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'

6. Kill all event Inspector processes:
Declare hcforeach cursor global for select 'Kill '+ rtrim (spid) from Master. DBO. sysprocesses
Where program_name in ('SQL filer', n' SQL event profiler ')
Exec sp_msforeach_worker '? '

7. Record Search:
Starting with N records
Select Top N * from table
-------------------------------
N to M records (primary index ID required)
Select top M-N * from table where ID in (select top m id from Table) order by ID DESC
----------------------------------
N to the end record
Select Top N * from Table order by ID DESC

8. How to modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'

9: Get all user tables in the current database
Select name from sysobjects where xtype = 'U' and status> = 0

10: Get all fields of a table
Select name from syscolumns where id = object_id ('table name ')

11. View views, stored procedures, and functions related to a table
Select a. * From sysobjects A, syscomments B where a. ID = B. ID and B. Text like '% table name %'

12: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = 'P'

13: Query all databases created by the user
Select * from Master .. sysdatabases d Where sid not in (select Sid from Master .. syslogins where name = 'sa ')
Or
Select dbid, name as db_name from Master .. sysdatabases where Sid <> 0x01

14: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'

[N]. [title]:
Select * From tablename order by customername

[N]. [title]:
Select * From tablename order by customername

 

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

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 stored procedures and functionsSource code
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 application firstProgramThe referenced table that has an inconsistency error, and then restore 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 deleting corrupted text objects, these fixes may cause some data loss.
the repair operation can be completed under the user transaction to allow the user to roll back the changes.
If rollback is fixed, the database will still contain errors and should be restored from the backup.
if an error fix is missing due to the level of severity provided, the fix is missing.
after the restoration is completed, back up the database.
repair_fast Performs small and time-consuming repair operations, such as fixing the 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 ).
when performing these repairs, there is no risk of data loss.