常用SQL语句总结 (内容会递增)

// 内容会递增
// 适用于MS SQL SERVER



-- 列举所有表

SELECT * FROM SYS.SYSOBJECTS WHERE [TYPE] = 'U' ORDER BY [NAME]

-- 列举所有存储过程

SELECT * FROM SYS.ALL_OBJECTS WHERE [TYPE] = 'P' AND IS_MS_SHIPPED = 0 ORDER BY [NAME]


SELECT [name],system_type_id,max_length,is_nullable FROM
           sys.columns where object_id = 1733281630 ORDER BY [name]

-- 查询数据表的大小

select * FROM sys.columns where object_id = 1733281630

select * FROM sys.all_columns where object_id = 1733281630

-- 删除SP

DROP PROC

-- 删除表数据（不记录日志，速度比delete块）

TRUNCATE TABLE tablename

-- 游标简单使用

DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

-- 09/09/15 新增内容

-- 建立主键

ALTER TABLE table_name
ADD
CONSTRAINT PK_table_name
PRIMARY KEY CLUSTERED (key_field_name)

-- 删除主键

ALTER TABLE table_name DROP CONSTRAINT PK_table_name

-- 建立索引

CREATE INDEX IDX_table_name ON table_name(idx_field_name)

-- 删除索引


DROP INDEX table_name.index_name 或者DROP INDEX index_name ON table_name-- 创建聚集索引CREATE CLUSTERED INDEX IDX_table_name ON table_name(field_name)