// 内容会递增
// 适用于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)
|
阅读(1034) | 评论(0) | 转发(0) |