SqlServer 数据库常用 SQL 语句

表格

-- 清空表
TRUNCATE TABLE [TableName];

-- 表格新增列
ALTER TABLE [TableName] ADD [ColumnName] NVARCHAR(255);

-- 新增列 Order 并设置默认值为 0
ALTER TABLE [TableName] ADD [Order] int NOT NULL CONSTRAINT DF_TableName_Order DEFAULT 0;

-- 删除列
ALTER TABLE [TableName] DROP COLUMN [ColumnName];

外键约束

-- 查询指定表格外键约束
SELECT * FROM (
	SELECT
		f.name AS ForeignKeyName,
		OBJECT_NAME(f.parent_object_id) AS TableName,
		COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
		OBJECT_NAME(f.referenced_object_id) AS ReferencedTableName,
		COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
	FROM
		sys.foreign_keys AS f
	INNER JOIN
		sys.foreign_key_columns AS fc
	ON
		f.OBJECT_ID = fc.constraint_object_id
) AS t
WHERE
	t.TableName='TableName' -- 表名
;

-- 移除表格外键约束， FK_name 为外键约束名称
ALTER TABLE [TableName] DROP CONSTRAINT FK_name;

-- 添加外键约束
ALTER TABLE [TableName] ADD CONSTRAINT FK_name
    -- 外键列名称
	FOREIGN KEY ([DocumentId])
    -- 主表与列名
	REFERENCES [Documents]([Id])
	;

索引

-- 新建索引
CREATE INDEX [IndexName] ON [TableName]([ColumnName], [ColumnName2]);

-- 删除索引
DROP INDEX [IndexName] ON [TableName];