"sp_spaceused" procedure is really handy to get the updated information about space usage by database or tables.
I combined the same with another very useful procedure "sp_MSforeachtable" to get this information for all user-defined tables.
If you see any problem with the information then just change the last parameter and set it to TRUE (FALSE in the script). That will update the space usage and then report updated details.
Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com
CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18)) EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE' SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace FROM #temp ORDER BY TableName DROP TABLE #temp
Stored procedure to return the list of columns of the primary key for a table. The name of the column for the result set is the name of the primary key for the table.
2006-09-14 (first published: 2006-09-07)
690 reads
This script is intended for SQL Server professionals that needs to have a quick snapshot from an environment with all relevant information about the databases. It's a compilation from multiple scripts, and works in SQL Server 2000.I think that works on 7.0 too, but may have some compilation issues.Don´t forget to see the section MANUAL […]
2003-04-28
4,706 reads
This query will give you the table name and column name on which you have defined the primary key. You can use this to get columns on which the primary key is based. I find it very useful when i need to define the FK relationship for other tables in PowerDesigner. Try to do the […]
2003-04-16
1,638 reads
This script returns the Duplicate indexes on any database (if any). Compatible with SQL Server 2000 only. Latest update Checks for possible duplications and removes them. Further, additional fields are displayed to support decision making.
2003-02-20
1,124 reads
This script creates an error message with a valid error number, and alert associated with the error message. (everything when it doesn't exists). Then captures all the user indexes in the database and executes DBCC ShowContig for each one. It calculates the % of fragmentation on leaf level and fires the alert when is fragmented.Then […]
2002-09-10
2,291 reads
