catalog

Create table
Update table
Delete table
Query table structure
Create view

Create table

Create table TestSql

CREATE TABLE TestSql(
Id bigint identity(1,1) not null PRIMARY key
)

reference material

SQL CREATE TABLE statement
SQL PRIMARY KEY constraint

Update table

Create field

Create a normal field

--ALTER TABLE table_name ADD column_name datatype
ALTER TABLE TestSql ADD Name varchar(10)
--Create a field that is not empty
ALTER TABLE TestSql ADD Sex int not null

reference material

SQL undo indexes, tables, and databases

Modify field

Modify the data type of the field

--ALTER TABLE table_name ALTER COLUMN column_name datatype
--Modify data type( eg.varchar(10)->varchar(20))，success
ALTER TABLE TestSql ALTER COLUMN Name_Update varchar(20)
--Modify data type( eg.varchar->int)，With index, modification failed
ALTER TABLE TestSql ALTER COLUMN Name int

Modify field name

--Example 1 Watch TABLE Columns of tid Change to id
EXEC sp_rename @objname = 'TABLE.tid', @newname = 'id', @objtype = 'column'
 
--Example 2 (abbreviation) a table TABLE Columns of tid Change to id
EXEC sp_rename 'TABLE.tid', @newname = 'id', @objtype = 'column'

--be careful: Changing any part of the object name can break scripts and stored procedures.
EXEC sp_rename 'TestSql.Name', @newname = 'NewName', @objtype = 'column'

Modify table name

--Example 1 Watch TABLE1 Change to TABLE2
EXEC sp_rename @objname = 'TABLE1', @newname = 'TABLE2'

--Example 2 (abbreviation) a table TABLE1 Change to TABLE2
EXEC sp_rename 'TABLE1','TABLE2'

--be careful: Changing any part of the object name can break scripts and stored procedures.
EXEC sp_rename 'TestSql','TestSql2'

reference material

SQL Server modify table name, field name, index name

Foreign key

Add a TestSqlChild table. The TestSqlChild table has a field ParentId that depends on the primary key Id of TestSql
Create foreign key

--ALTER TABLE target_table_name
--ADD CONSTRAINT FOREIGN_KEY_Name
--FOREIGN KEY (target_table_column)
--REFERENCES table_name(table_name_column)

ALTER TABLE TestSqlChild
ADD CONSTRAINT fk_ParentId
FOREIGN KEY (ParentId)
REFERENCES TestSql(Id)

Delete foreign key

--ALTER TABLE target_table_name
--DROP CONSTRAINT FOREIGN_KEY_Name

ALTER TABLE TestSqlChild
DROP CONSTRAINT fk_ParentId

reference material

SQL FOREIGN KEY constraint

Indexes

Create index

--CREATE INDEX index_name ON table_name (column_name)
--Create a normal index
CREATE INDEX NameIndex ON TestSql (Name)
--Create unique index
CREATE UNIQUE INDEX NameIndex ON TestSql (Name)

reference material

SQL CREATE INDEX statement
Delete index

--DROP INDEX table_name.index_name
DROP INDEX TestSql.NameIndex

Index of query target field
Using stored procedure queries

--use database_name EXEC Sp_helpindex table_name
--perhaps
--EXEC Sp_helpindex table_name
EXEC sp_helpindex 'TestSql'

The results are as follows:

Delete field

--ALTER TABLE table_name DROP COLUMN column_name
--If the field has an index, you need to delete the index before you can delete the field (foreign key similar)
ALTER TABLE TestSql DROP COLUMN Sex

Delete table

Delete table TestSql

DROP TABLE TestSql

Query table structure

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'
ORDER BY ORDINAL_POSITION

perhaps

sp_Columns 'tableName'

Include comment information

SELECT A.name AS table_name,B.name AS column_name,D.IS_NULLABLE,D.DATA_TYPE,D.CHARACTER_MAXIMUM_LENGTH,C.value AS column_description

FROM sys.tables A INNER JOIN sys.columns B

ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C

ON C.major_id = B.object_id AND C.minor_id = B.column_id

INNER join INFORMATION_SCHEMA.COLUMNS D on D.COLUMN_NAME=B.name and D.table_name=A.name

WHERE A.name ='tableName'

reference material

Get Table information (Column Name, Data Type) in SQL Server
All table queries and annotation description queries of SQL Server

Create view

--CREATE VIEW view_name AS
--SELECT column_name(s)
--FROM table_name
--WHERE condition

CREATE VIEW QueryTestSql AS
SELECT *
FROM TestSql
WHERE 1=1

--use
select * from QueryTestSql

reference material

SQL VIEW (view)