獲取SQL SERVER用戶表信息游標

/獲取用戶表信息游標/
SET NOCOUNT ON
DECLARE find_user_table CURSOR
FOR
SELECT [name],crdate FROM sysobjects
WHERE type='U' /這裡只查詢用戶定義的表/
ORDER BY [name]

DECLARE @cName VARCHAR(128) /定義儲存表名變量/
DECLARE @crdate DATETIME /定義表創建日期變量/
DECLARE @Rows INT /定義表行數變量/

CREATE TABLE #tmpTable /創建用來儲存信息的臨時表/
(
Tablename VARCHAR(128),
crDate datetime,
Row INT
)
OPEN find_user_table /打開游標/
FETCH NEXT FROM find_user_table INTO @cName,@crdate /從游標中讀取表名到變量/
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @cSql nvarchar(500),@par nvarchar(30)
SELECT @par='@nRows INT OUTPUT'
SELECT @cSql='SELECT @nRows=COUNT(*) FROM ['+@cName+']'
EXECUTE sp_executesql @cSql,@par,@rows OUTPUT /計算當前表的總行數/

INSERT INTO #tmpTable valueS(@cName,@crdate,@rows) /將當前表信息存儲到臨時表/
FETCH NEXT FROM find_user_table INTO @cName,@crdate /從游標中讀取表名到變量/
END
SELECT * FROM #tmpTable /顯示所有表信息/
DROP TABLE #tmpTable /刪除臨時表/
CLOSE find_user_table /關閉游標/
DEALLOCATE find_user_table /釋放游標/