To find out how much disk space you Microsoft SQL Server database consumes and other things, you can run the following T-SQL procedures inside the Query Analyzer.
sp_spaceused @updateusage = 'TRUE'
To see quickly how many stored procedure, views, tables and other objects your database has you can use this query here
See table disk space usage
sp_spaceused 'Tablename'
See all tables
select *
from INFORMATION_SCHEMA.tables
See all foreign key constraints
select *
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
See foreign keys, primary keys of a particular table
select
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
See the foreign key of a particular table.
select table_name, column_name "foreign key", constraint_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where TABLE_NAME='put_table_name_here'
and constraint_name not like 'PK%'
Find which MS SQL Server Stored procedure is using a particular table, or in other words dependencies between stored procedures and tables.
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%put_table_name_here%' -- enter table name here.
For more info go to: http://msdn.microsoft.com/en-us/library/aa933209(SQL.80).aspx