SQL Server database statements and some operations

By the end of the year, I have made some preparations for my work and study problems and some common knowledge points for future use. This article involves the database, which is a summary of development (1) --- supplement to the database article. 1. For tables with an Identity configured for the primary key, insert data into the table after deleting the table data. The Identity column does not start from 1, as shown in figure

By the end of the year, I have made some preparations for my work and study problems and some common knowledge points for future use. This article involves the database, which is a summary of development (1) --- supplement to the database article. 1. For tables with an Identity configured for the primary key, insert data into the table after deleting the table data. The Identity column does not start from 1, as shown in figure

By the end of the year, I have made some preparations for my work and study problems and some common knowledge points for future use. This article involves the database, which is a summary of development (1) --- supplement to the database article.

1. For tables with the primary key configured with Identity, insert data into the table after deleting the table data. The Identity column does not start from 1. If you want to delete the data, the Indentity column still starts from 1, you can use the following code to delete data.

truncate table tablenameDBCC CHECKIDENT(tablename,RESEED,1)

2. Determine whether the specified table exists in the Database

if exists(select name from sysobjects where name='tablename' and type='u')

3. Determine whether the specified Column exists in the specified table

if exists(select * from sys.columns,sys.tables       where sys.columns.object_id = sys.tables.object_id      and sys.tables.name='tablename' and sys.columns.[name]='columnname')

4. When writing a program such as a code generator, you usually need to retrieve all the table names in the database and some basic information about the table fields, such as the field length, field type, and description. The preceding SQL statement is as follows:

-- Select * from sysobjects where xtype = 'U' order by name -- obtain some basic information about fields in the table select sys. columns. name, -- field name sys. types. name as typename, -- field type sys. columns. max_length, -- field length sys. columns. is_nullable, -- whether it can be null (select count (*) from sys. identity_columns where sys. identity_columns.object_id = sys. columns. object_id and sys. columns. column_id = sys. identity_columns.column_id) as is_identity, -- whether to auto-increment (select value from sys. extended_properties where sys. extended_properties.major_id = sys. columns. object_id and sys. extended_properties.minor_id = sys. columns. column_id) as description -- comment from sys. columns, sys. tables, sys. typeswhere sys. columns. object_id = sys. tables. object_id and sys. columns. system_type_id = sys. types. system_type_id and sys. tables. name = 'tablename' order by sys. columns. column_id

5. Use transactions in the Stored Procedure

Create procedure procnameasbegin tran -- execute the SQL statement if @ ERROR! = 0 begin rollback tran -- failed end else begin commit tran -- Success end

6. Clear database logs

Dump transaction DatabseName WITH NO_LOGBACKUP LOG DatabseName WITH NO_LOG dbcc shrinkfile (DatabseLogName, 1) -- DatabseName is the database name -- DatabseLogName is the LOG file name. You can use the following statement to obtain -- select name from sysfiles

Another simple method is to separate the database, delete the log file, and attach the database. The generated log file is only over 500 kb.

The following describes several common system stored procedures and functions.

7 db_name () Get the Database Name

Select db_name () Test (1 row affected)

8. object_id can be used to obtain the number of an object in the system. objects include tables, views, and stored procedures. If the table does not exist, null is returned, so it can be used to determine whether the table exists.

Select object_id ('objectname') -- determines whether the table exists if object_id ('tablename') is not null

9 sp_helptext is used to obtain the text of objects such as views and stored procedures. It can be found quickly, but it will change the format of views or stored procedures. Therefore, this system stored procedure is usually used for viewing. If you want to modify a stored procedure, I will still find the stored procedure through the tree menu and modify and save it.

sp_helptext 'objectname'

10 parsename: Get the specified part of the object name. This function has two parameters: the first is the object name, and the second is the code of the specified part.

Select parsename ('ec2003. databasename. dbo. tablename ', 1) -- The Object Name returns tablenameselect parsename ('ec2003. databasename. dbo. tablename ', 2) -- Schema name returns dboselect parsename ('ec2003. databasename. dbo. tablename ', 3) -- database name returns databasenameselect parsename ('ec2003. databasename. dbo. tablename', 4) -- the server name returns oec200

Let's write so much about it first. It will be supplemented later.