EXEC sp_databases; -- 列庫
EXEC sp_tables; -- 列表
EXEC sp_columns 'Student'; -- 列表結構
EXEC sp_helpindex 'Student';-- 索引
EXEC sp_helpconstraint 'Student'; -- 約束
EXEC sp_stored_procedures; -- 所有存儲過程
EXEC sp_helptext 'sp_who'; -- 查看定義
EXEC sp_rename 'Student', 'StuInfo'; -- 改表名
EXEC sp_rename 'StuInfo.Name', 'StuName', 'COLUMN'; -- 改列名
EXEC sp_renamedb 'OldDB', 'NewDB'; -- 改库名
CREATE PROC usp_GetAllStudents AS SELECT * FROM Student;
CREATE PROC usp_GetById @Id INT = 0 AS SELECT * FROM Student WHERE Id = @Id;
CREATE PROC usp_GetNameAge @Id INT, @Name VARCHAR(20) OUTPUT, @Age INT OUTPUT
AS SELECT @Name = Name, @Age = Age FROM Student WHERE Id = @Id;
-- 調用
DECLARE @N VARCHAR(20), @A INT;
EXEC usp_GetNameAge 1, @N OUT, @A OUT;
SELECT @N, @A;
CREATE PROC usp_Search @Name VARCHAR(20) = '%' AS
SELECT * FROM Student WHERE Name LIKE @Name;
CREATE PROC usp_VolatileData WITH RECOMPILE AS SELECT * FROM BigTable WHERE DateCol > DATEADD(day,-1,GETDATE());
適用:參數嗅探導致計劃不穩、臨時表統計變化大、極低頻執行。
CREATE PROC usp_Secret WITH ENCRYPTION AS SELECT * FROM Payroll;
**不可逆**,部署前必備份腳本。sp_helptext 返回加密提示。
CREATE PROC usp_CursorOut @Cur CURSOR VARYING OUTPUT AS
SET @Cur = CURSOR FORWARD_ONLY STATIC FOR SELECT Id, Name FROM Student;
OPEN @Cur;
-- 調用
DECLARE @C CURSOR, @Id INT, @Name VARCHAR(20);
EXEC usp_CursorOut @Cur = @C OUTPUT;
FETCH NEXT FROM @C INTO @Id, @Name;
WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Id; FETCH NEXT FROM @C INTO @Id, @Name; END
CLOSE @C; DEALLOCATE @C;
CREATE PROC usp_Page @PageSize INT, @PageNum INT AS
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RN, * FROM Product
) T WHERE RN BETWEEN (@PageNum-1)*@PageSize+1 AND @PageNum*@PageSize;
ALTER PROC usp_GetById @Id INT AS SELECT * FROM Student WHERE Id = @Id;
DROP PROC usp_GetById;
SELECT name FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;