/*獲取用戶表信息游標*/ 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 /*釋放游標*/
|