/ * Get the user table information cursor * / set nocount overdeclare find_user_table cursoorforselect [name], crdate from sysobjectswhere type = 'u' / * Here you only query user-defined table * / Order by [name]
Declare @cname varchar (128) / * Define Save Table Name Variable * / Declare @CRDATE DATIME / * Definition Table Create Date Variable * / Declare @Rows INT / * Defining Table Route Variables * /
CREATE TABLE #TMPTABLE / * Create a temporary table for stocking information * / (TableName Varchar (128), CRDATE DATETIME, ROW INT) Open Find_User_Table / * Open Cursor * / Fetch next from find_user_table @ cname, @ CRDATE / * Read the table name to the variable * / while @@ fetch_status = 0begin declare @csql nvarchar (500), @ par nvarchar (30) select @par = '@ nrows int outprut' selection @ csql = 'select @ nrows = count (*) From [' @ cname '] 'execute sp_executesql @ csql, @ par, @ rows output / * calculate the total number of current tables * /
INSERT INTO #TMPTable VALUES (@ cname, @ crdate, @ rows) / * Store the current table information to temporary table * / fetch next from find_user_table @ cname, @ CRDATE / * Read the table name from the cursor * / Endselect * from #tmptable / * Show all table information * / Drop table #tmptable / * Delete temporary table * / close find_user_table / * Close Cursor * / DEAllocate Find_user_table / * Release Cursor * /
