sql server administration commands

Label:One, database size query1. exec sp_spaceused ' table name '-(SQL statistics, may not be allowed after a large number of transactional operations)2. exec sp_spaceused ' table name ', True--(exact table space size, but may take some time to count)3. Exec sp_msforeachtable "exec sp_spaceused '?" --(All user tablespace table size, SQL statistics, may not be allowed after a large number of transaction oper

databaseSp_helpindex @objnameYou can also use the Sp_nchelpindex process to see more detailed index conditionsSp_nchelpindex @objnameView constraint information for a data object under a databaseSp_helpconstraint @objname12. How to shrink a database log fileShrink the simple Recovery model database log, @database_name_log size in units of MBackup LOG @database_name with NO_LOGDBCC SHRINKFILE (@database_name_log, 5) 13. Known column name, lookup table Select Tab.name from syscolumns as col inne

Tags: new prim ble SQL insert let a trunc intoSQL PRIMARY KEY ConstraintPrimary key must be unique, primary key column cannot be nullEach table should have a primary key, and each table can have only one primary key Foreigh KEY to learn Create a tableCreate Table TableName(Id int not NULL Primary key,Name varchar (255) is not NULL,Tell varchar (255),ADD varchar (255)) Increase:Insert into TableName (column 1, column 2) VALUES (value1,value2)INSERT in

pointer to the last row of the data tablers.absoluteposition=n Move the record pointer to the nth row of the data tablers.absolutepage=n Move the record pointer to the first row of page Nrs.pagesize=n set to N records per pageRs.pagecount Returns the total number of pages based on pagesize settingsRs.recordcount Returns the total number of recordsRs.bof Returns whether the record pointer is above the first end of the data table, True indicates Yes, False nors.eof Returns whether the record poin

MS SQL Server provides a number of commands for database repairs that can be attempted when the database is challenged or some cannot complete the read. 1. DBCC CHECKDB After restarting the server, perform the following SQL database repairs in

value of a column sum (column) returns the sum of a column example: Selectavg (age) as aveagefromsalemanager *//*sql index There are two kinds, clustered index and nonclustered index, the clustered index storage record is physically continuous, and the nonclustered index is logically continuous, the physical storage is not contiguous. Clustered index A table can have only one, and a table of non-clustered indexes may exist multiple. */--CREATE VIEW

Create a databaseCREATE DATABASE database_nameExample: CREATE DATABASE TestExecute commands first when creating a tableUse testCreate a tableCREATE TABLE table name (column name 1 data type, column name 2 data type, ... ）Example: Create table book (name char), writer varchar (20))You can set some fields to be self-increment when you create a table, but the field property must be of type intThe CREATE TABLE table name (column name int identity (A, b))

Label: --The easiest way is to use the SSM client, report view --querying the spatial status of data files DBCCShowfilestats--Querying the space of log files DBCCsqlperf (logspace)--querying the space situation in tempdb Select * fromSys.dm_db_file_space_usage--space usage for query tables execSys_viewtablespace--use caution and have an impact on performance--DBCC Showconfig () --Log Diagnostic Methods SelectName,recovery_model_desc, Log_reuse_wait,log_reuse_wait_desc fromsys.databaseswhereNam

', AK_Product_Name; GO 4. Update statistical information Use AdventureWorks2012; GO -- UPDATE STATISTICS for all tables in the library EXEC sp_updatestats Go -- UPDATE STATISTICS for a table UPDATE STATISTICS Sales.SalesOrderDetail; GO -- UPDATE STATISTICS for an index UPDATE STATISTICS Sales.SalesOrderDetail Ak_salesorderdetail_rowguid; 5. View database Statistics settings SELECT Case whenDATABASEPROPERTYEX ('Dbmonitor','Isautocreatestatistics')= 1 Then 'Yes'

Label:Establish dept and EMP Tables, and set the foreign keys of the EMP table with the dept_id in the Dept table CREATE TABLE Dept ( dept_name nvarchar () not NULL, dept_address nchar (+), dept_id int constraint pk_ Dept PRIMARY key --dept table's primary key, named Pk_dept ) CREATE TABLE emp ( emp_name nvarchar () not NULL, Emp_sex nchar (1), emp_age int, emp_id int constraint pk_emp primary key, --emp table primary key dept_id int Constraint fk_emp_dept foreign key refere

Tags: SRE pre distrib amp release max insert gets upperOriginal: SQL Server monitors the number of commands that are not distributed in the publicationWe usually look at the number of commands that are not distributed. However, when the server has a lot of releases, it is t

lock when it is selected. Tablockx (row it table lock)When this option is selected, SQL Server will place an exclusive lock on the entire table until the command or transaction ends.This prevents other processes from reading or modifying the data in the table. HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal

Original: Chapter III-Monitoring SQL Server using system functions, stored procedures, and DBCC SQLPERF commands (3)This is the last article in this series. How the DBCC command will be used to monitor the usage of SQL Server log space.Objective:Each database must have a tra

You can use the dtexec command in SQL Server to run SSIS packages (more than 2005 versions), and of course you can run SSIS packages through system procedures: xp_cmdshell invoke dtexec. The specific steps are as follows: 1. First of all, it is of course to design the package in business intelligence and debug it through. 2. Then, there are two ways to run SSIS packages using

In general, we can use SQL Server under DOS commands, using the DOS command to use SQL Server as follows:1. Connect to the database server:Using the osql command, this command can enter osql in a DOS window? View, such as:Next we use Osql-s localhost-u sa-p 123456 to connect

Label:Http://www.ynpxrz.com/n822732c2024.aspxWe know: SQL Server restart is divided into two steps.1. Stopnet stop MSSQLServer2. RestartNET start MSSQLServer3. Add MySQL stop, start commandnet stop MySQLnet stop MySQLBookmark this page. You won't forget it next time.In web development often need to restart SQL Server,

' Favorite Movies ' [Email protected] Effect: 6.RAISERROR The RAISERROR command is used to return the user-specified information when an error message is returned in the SQL Server system 7.READTEXT The READTEXT command reads the value in the text, ntext, or image column, starting at the specified position to read the specified number of characters. The format is as follows: READTEXT {table. column [HOL

Tags: style blog ar color OS using SP strong onThe DBCC (database Base consistency Checker, DB consistency checker) commands are used to verify database integrity, find errors, and analyze system usage.1. DBCC CheckallocChecks the consistency of the disk space allocation structure for the specified database.Grammar:DBCCCheckalloc[(database_name | database_id | 0 [, NOINDEX |, {repair_allow_data_loss | Repair_fast | Repair_rebuild}] ) [With {[All

SQL commands for regularly deleting historical data from databases and SQL commands for historical data Create a job,Used to regularly clear historical database data Delete the data of the first quarter of the table before gps_comm, as follows: Elete from gps_comm WHERE (date If you want to continue to delete the dat

-pooling, determine whether the SQL server has a network connection bottleneck.Overview of Network Services　Communication between the SQL Server client and the server requires a variety of software and hardware layers. Let's take a brief look at these levels. Their functions