SQL view database environment and some parameters

SQL view database environment and some parametersSelectParent_obj fromsysobjectswhereName='fk_student_banjiid'  --get the Appearance ID based on the foreign key nameSelect *  fromsysobjectswhereXtype='U'  --All table information under the current databaseSelect *  fromSysforeignkeys--table with primary foreign key relationship Rkeyid column as referenced table (primary table) Fkeyid column as foreign key table (appearance)get the primary table name based on the foreign key:SelectName fromsysobjectswhereXtype='U'  andId=(SelectRkeyid fromSysforeignkeyswhereFkeyid=(SelectParent_obj fromsysobjectswhereName='fk_student_banjiid')) 1. View the version of the databaseSelect @ @version    2. View the machine operating system parameters of the databaseexecMaster.. xp_msver3. View database-initiated parameters sp_configure4. View database Startup timeSelect Convert(varchar( -), Login_time, -) fromMaster.. sysprocesseswherespid=15.--View the database server name and instance name  Print 'Server Name .........:' +  Convert(varchar( -),@ @SERVERNAME)       Print  'Instance ............:' + Convert(varchar( -),@ @SERVICENAME)      6. View all database names and sizes sp_helpdb7. Renaming a database with SQL Sp_renamedb'Old_dbname','New_dbname'8. View all database user login information sp_helplogins9. To view the role information that all database users belong to Sp_helpsrvrolemember repairing orphaned users when migrating a server, the Fix_orphan_user script or loneuser process that can be used to change the user of a data object is the primary SP _changeobjectowner[@objectname =] 'Object',[@newowner =] 'owner'Note: Changing any part of an object name can break scripts and stored procedures. Back up the database user login information on one server you can use the Add_login_to_aserver script to view object-level user rights under a database sp_helprotectTen. View Linked server Sp_helplinkedsrvlogin One. View remote Database User login information Sp_helpremotelogin A. View the size of a data object under a database sp_spaceused@objnameYou can also use the sp_toptables process to see the maximum n (default 50) Table -. View index information for a data object under a database Sp_helpindex@objnameYou can also use the Sp_nchelpindex process to see more detailed index conditions Sp_nchelpindex@objnameThe clustered index is the physical order in which the records are arranged, and the index occupies less space.   Tables with very frequent key-value DML operations I recommend using non-clustered indexes and constraints, with default values for the FILLFACTOR parameter.  -. View the constraint information for a data object under a database Sp_helpconstraint@objname  the. View all stored procedures and functions in the database Use @database_namesp_stored_procedures View the source code of stored procedures and functions sp_helptext'@procedure_name'view contains a string@str Data Object name     Select distinct object_name(ID) fromsyscommentswhere text  like '% @str%'create an encrypted stored procedure or function to decrypt encrypted stored procedures and functions in front of the As with the encryption parameter you can use the Sp_decrypt procedure -. View information about users and processes in the database sp_who view information about active users and processes in a SQL Server database sp_who'Active'  -. View the condition of the lock in the SQL Server database sp_lock process number 1--50 is used internally by the SQL Server system, and the process number greater than 50 is the user's connection process. SPID is the process number, dbid is the database number, ObjID is the data object number to view the SQL statement that the process is executingDBCCInputBuffer () recommend that you use the improved SP_WHO3 process to see directly the SQL statements that the process is running SP_WHO3 check the deadlock with the Sp_who_lock process Sp_who_lock -how to view and Shrink database log files View all database log file sizesDBCCSqlperf (logspace) If some log files are large, shrink the simple Recovery model database log, after shrinkingthe size of the @database_name_log is measured in M     Backup Log @database_name  withNo_logDBCCShrinkfile (@database_name_log,5) +. Methods for parsing SQL Server SQL statements:Set StatisticsTime { on |  off}Set StatisticsIO { on | off} graphically displays the query execution plan in Query Analyzer -Inquire -Show estimated assessment plan (D)-Ctrl-L or click Graphical text in the toolbar to display the query execution planSetShowplan_all { on | off}SetShowplan_text { on | off}Set  StatisticsProfile { on | off } -. When there is an inconsistency error, the NT Event Viewer has an error of No. 3624, and the method of repairing the database first comments out the inconsistent error table referenced in the application, and then restores and repairs the backup or other machine first.Alter Database  [@error_database_name] Setsingle_user fix a table with inconsistent errorsDBCCCheckTable ('@error_table_name', Repair_allow_data_loss) or unfortunately choose to fix small database names with inconsistent errorsDBCCCheckdb'@error_database_name', Repair_allow_data_loss)Alter Database  [@error_database_name] SetMulti_user CHECKDB has 3 parameters: Repair_allow_data_loss includes assigning and reassigning rows and pages to correct assignment errors, structural rows or page errors, and deleting corrupted text objects that may cause some data to be dropped    Lost.     The repair operation can be completed under a user transaction to allow the user to roll back the changes.    If the repair is rolled back, the database will still contain errors and should be recovered from the backup.     If a bug's fix is omitted due to the level of repair provided, any fix that depends on the fix will be omitted.     After the repair is complete, back up the database.     Repair_rest perform small, time-consuming repair operations, such as repairing additional keys in a nonclustered index.     These fixes can be done quickly and there is no risk of losing data.      Repair_rebuild performs all the repairs done by repair_rest, including repairs that take a long time (such as rebuilding the index). There is no risk of data loss when performing these repairs.  +. View table Constraints Sp_helpconstraint tablename sp_help tablename--View table structure, indexes, constraints, etc. A. Add User sp_addlogin LoginName at. Change the default database after user connection sp_defaultdb loginname,tablename -. View objects in the current databaseSelect *  fromsysobjectswhereXtype='U'xtype can be: xtypeChar(2) object type. Can be one of the following object types: C= CHECKConstraint D=Default Value orDEFAULTConstraint F= FOREIGN KEYConstraint L=Log FN=Scalar functionsIF =Inline table function p=Stored Procedure PK= PRIMARY KEYConstraint (type is K) RF=Copy Filter Stored procedure S=system table TF=Table function tr=Trigger U=User Table UQ= UNIQUEConstraint (type is K) V=View X=Extended stored Procedures -. Add a default value constraint for an existing columnAlter TableTableNameAdd constraint [ConstraintName]default{Expression|(0)} forColumnName -. Adding a primary key to an existing columnAlter TableTableNameAdd constraint  [ConstraintName] Primary  Key(ColumnName) -. Returns the physical name and attributes of the file associated with the current database sp_helpfile -. View the table name of the constraintSelect  *  fromsysobjectswhereId=(SelectParent_obj fromsysobjects Wherename='ConstraintName'Parent_obj: The object identification number of the parent object (for example, for a trigger or constraint, which is the table ID)

SQL view database environment and some parameters