tables sizes and recovery models
-
HI.. I have 2 questions...
I would like to know how i can get all the sizes of all the tables in a database
and also how can i get a listing of all the recovery models for all databases.
Juanita
-
You can use the stored procedure sp_spaceused to obtain the size information for each table. You will need to supply the table name as a parameter, so if you want all tables in a database I would suggest using a cursor. For the recovery modes you can execute the following command on each database:
SELECT DATABASEPROPERTYEX(N'Your DB Name', N'RECOVERY')
Again, if you need to obtain this information for an entire server you could use a cursor as well.
-
HI.. What exaclty is a cursor? I've never worked with that before?
Juanita
-
Here is an example that will generate the sp_spaceused statements for every user table in a database.
DECLARE @l_table_name VARCHAR(255)
DECLARE CHECKSIZE CURSOR For
SELECT name FROM sysobjects WHERE xtype LIKE 'U'
OPEN CHECKSIZE
FETCH NEXT FROM CHECKSIZE INTO @l_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXECUTE sp_spaceused '+''''+@l_table_name+''''
FETCH NEXT FROM CHECKSIZE INTO @l_table_name
END
CLOSE CHECKSIZE
DEALLOCATE CHECKSIZE
Once you run this command you can copy the results to a new query analyzer window and there you have all your sp_spaceused commands. It's a fast way to generate a repetiive list without having to manually type in all the table names.
-
Or even faster for the same result without a cursor :
SELECT 'EXECUTE sp_spaceused ''' + name + '''' FROM sysobjects WHERE xtype LIKE 'U' ORDER BY name
The advantage of the cursor in this case is that you can execute the statement instead of only printing it which can help created un-maned jobs.
-
Thank you so much !!! These ideas are perfect !!!
Juanita
-
Or:
EXECUTE sp_MSforeachtable
@command1=" Print '?' EXECUTE sp_spaceused '?'"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply