sp_spaceused and other MS SQL Server Dictionary views

Many times I am asked to look at Microsoft SQL Servers to investigate things, and I find the following T-SQL procedures and Microsoft SQL Server dictionary views very handy.


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