| Back to logs list
326378 2010 年 12 月 09 日 14:28 Reading (loading. ..) Comments (0) Category: SQL database
This article mainly introduces the commonly used SQL Server database management SQL and T-SQL statement.
1. View the database version of the
select @ @ version
2. View the database where the machine operation parameters
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
See the database server name and instance name
print 'Server Name ...............:' + convert (varchar (30), @ @ SERVERNAME)
print 'Instance ..................: '+ convert (varchar (30), @ @ SERVICENAME)
5. view all the database name and the size of the
sp_helpdb
rename the database using SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. view all database user login information
sp_helplogins
view all the database user belongs to role information
sp_helpsrvrolemember
repair orphaned users migrate server, you can use the script or LoneUser process fix_orphan_user
users change a data object is the main
sp_changeobjectowner [@ objectname =] 'object', [@ newowner =] 'owner'
Note: Changing any part of the object name could break scripts and stored procedures.
the database on a server back up information from the user login script can be used add_login_to_aserver
7. View linked server
sp_helplinkedsrvlogin
view the remote database user login
sp_helpremotelogin
8. view a data object in a database the size of the next
sp_spaceused @ objname
sp_toptables process can also be used to see the largest N (default is 50) Table
view a data object in a database, the index of following information
sp_helpindex @ objname
SP_NChelpindex process can also view the index in more detail the situation
SP_NChelpindex @ objname
clustered index is the physical order of records according to the index account for less space.
DML operation is very frequent on the key of the table I s
uggest that a non-clustered indexes and constraints, fillfactor parameters with default values.
view a database under the constraints of a data object information
sp_helpconstraint @ objname
9. View the database of all the stored procedures and functions
use @ database_name
sp_stored_procedures
See stored procedures and functions source code
sp_helptext '@ procedure_name'
View @ str contains a string of data object names
select distinct object_name (id) from syscomments where text like ' % @ str% '
create an encrypted stored procedure or function in AS WITH ENCRYPTION parameter preceded
decrypt encrypted stored procedures and functions can be used sp_decrypt process
10 . View the database user and process information
sp_who
view the activities of SQL Server database users and process information
sp_who 'active'
view the SQL Server database locks the case
sp_lock
process ID 1 - 50 is used within the SQL Server system, the process is the number of users is greater than 50 the connection process.
spid is the process number, dbid is the database ID, objid is the data Object number
view the process being executed SQL statement
dbcc inputbuffer ()
recommend sp_who3 with the process through improved processes can run the SQL statement that
sp_who3
Check the deadlock with a sp_who_lock
process
sp_who_lock
11. shrink the database log file method
shrink the simple recovery model database log, the size of contraction @ database_name_log M
backup unit log @ database_name with no_log
dbcc shrinkfile (@ database_name_log, 5)
12. Analysis of the methods SQL Server SQL statement:
set statistics time on
set statistics io off
graphically display query execution plan in Query Analyzer
-> check - br>
text display query execution plan
set showplan_all on
set showplan_text off
set statistics profile on
13. inconsistency error, NT Event Viewer Error number 3624 in the repair method of the database application
first comment out the reference to the appearance of inconsistency in the wrong table, and then the backup or other machines first restore and then do the repair operation
alter database [@ error_database_name] set single_user
fixed table inconsistent errors
dbcc checktable ('@ error_table_name', repair_allow_data_loss)
inconsistencies or errors unfortunately choose to repair a small database name
dbcc checkdb ('@ error_database_name', repair_allow_data_loss)
alter database [@ error_database_name] set multi_user
CHECKDB 3 parameters :
repair_allow_data_loss including line and page allocation and deallocation to correct allocation errors, structural row or page errors,
and deletion of corrupted text objects, these repairs can cause some data loss.
repair operation can be done under a user transaction to allow the user to roll back the changes.
If repairs are rolled back, the database will still contain errors and should be restored from backup.
If for the sake of the omission to provide a fixed level of bug fixes, it will depend on the repair of any missing repair.
repair is completed, back up the database.
repair_fast be small, not time-consuming repair actions such as repairing the additional non-clustered index key.
these repairs can be done quickly, and no risk of losing data.
repair_rebuild complete implementation of all the repair_fast repair,
ugg ムートンブーツ, including the need to repair a long time (such as rebuilding the index.)
perform these repairs without the risk of data loss.