Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE World00_Character
- GO
- -- Create a cursor to loop through the System Ojects and get each table name
- DECLARE TBL_CURSOR CURSOR
- -- Declare the SQL Statement to cursor through
- FOR ( SELECT Name FROM Sysobjects WHERE TYPE='U' )
- -- Declare the @SQL Variable which will hold our dynamic sql
- DECLARE @SQL NVARCHAR(MAX);
- SET @SQL = '';
- -- Declare the @TblName Variable which will hold the name of the current table
- DECLARE @TblName NVARCHAR(MAX);
- -- Open the Cursor
- OPEN TBL_CURSOR
- -- Setup the Fetch While that will loop through our cursor and set @TblName
- FETCH NEXT FROM TBL_CURSOR INTO @TblName
- -- Do this while we are not at the end of the record set
- WHILE (@@FETCH_STATUS <> -1)
- BEGIN
- -- Appeand this table's select count statement to our sql variable
- SET @SQL = @SQL + ' ( SELECT '''+@TblName+''' AS Table_Name,COUNT(*) AS Count FROM '+@TblName+' ) UNION';
- -- Delete info
- EXEC('DBCC CHECKIDENT ('+@TblName+',RESEED,(SELECT IDENT_SEED('+@TblName+')))');
- -- Pull the next record
- FETCH NEXT FROM TBL_CURSOR INTO @TblName
- -- End the Cursor Loop
- END
- -- Close and Clean Up the Cursor
- CLOSE TBL_CURSOR
- DEALLOCATE TBL_CURSOR
- -- Since we were adding the UNION at the end of each part, the last query will have
- -- an extra UNION. Lets trim it off.
- SET @SQL = LEFT(@SQL,LEN(@SQL)-6);
- -- Lets do an Order By. You can pick between Count and Table Name by picking which
- -- line to execute below.
- SET @SQL = @SQL + ' ORDER BY Count';
- --SET @SQL = @SQL + ' ORDER BY Table_Name';
- -- Now that our Dynamic SQL statement is ready, lets execute it.
- EXEC (@SQL);
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement