sql to get table sizes and percentage of database size - run in required database

-- Create the temp table for further querying

CREATE TABLE #temp(

rec_id int IDENTITY (1, 1),

table_name varchar(128),

nbr_of_rows int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data

SELECT *

FROM #temp

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

how to install performance dashboard on sql server 2005 SP2

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

common table expressions article

http://www.sqlservercentral.com/articles/Development/commontableexpressionsinsqlserver2005/1758/

queries of dbcc showcontig to return various stuff

http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/analyzingdatastoragetenshowcontigqueries/1438/

find all constraints

/*

sqryConstraints

Created by Larry Ansley 6/10/03.

*/

-- Unique Constraints

Select

Case o1.xtype

When 'C' Then 'Check'

When 'D' Then 'Default'

When 'F' Then 'Foreign Key'

When 'PK' Then 'Primary Key'

When 'UQ' Then 'Unique'

Else 'Other' End as 'Constraint Type',

o.name as 'Table Name',

o1.name as 'Constraint/Index Name',

c1.name as 'Column Name',

k.keyno as 'KeyNo'

From sysobjects o

Join sysobjects o1

on o1.Parent_obj = o.id

Join sysconstraints c

on c.constid = o1.id

Join sysindexes i

on i.id = o.id

and i.name = o1.name

Join sysindexkeys k

on k.id = i.id

and k.indid = i.indid

Join syscolumns c1

on c1.id = k.id

and c1.colid = k.colid

Where o1.xtype = 'UQ'

Order By o.name, o1.name, k.KeyNo

-- Check, Default Constraints

Select

Case o1.xtype

When 'C' Then 'Check'

When 'D' Then 'Default'

When 'F' Then 'Foreign Key'

When 'PK' Then 'Primary Key'

When 'UQ' Then 'Unique'

Else 'Other' End as 'Constraint Type',

o.name as 'Table Name',

o1.name as 'Constraint Name',

c1.name as 'Column Name',

c.text as 'Default/Check Value'

From sysobjects o

Join sysobjects o1

on o1.Parent_obj = o.id

Join syscolumns c1

on c1.id = o1.parent_obj

and c1.colid = o1.info

Join syscomments c

on o1.id = c.id

Where o1.xtype In ('C' , 'D')

Order By o1.xtype, o.name, c1.name

-- Foreign Key Constraints

Select

Case o1.xtype

When 'C' Then 'Check'

When 'D' Then 'Default'

When 'F' Then 'Foreign Key'

When 'PK' Then 'Primary Key'

When 'UQ' Then 'Unique'

Else 'Other' End as 'Constraint Type',

o1.name as 'Constraint Name',

o.name as 'FK Table Name',

c1.name as 'FK Column Name',

c2.name as 'PK Column Name',

o2.name as 'PK Table Table',

fk.keyno as 'KeyNo'

From sysobjects o

Join sysobjects o1

on o1.Parent_obj = o.id

Join sysforeignkeys fk

on fk.constid = o1.id

Join sysobjects o2

on o2.id = fk.rkeyid

Left Join syscolumns c1

on c1.id = fk.fkeyid

and c1.colid = fk.fkey

Left Join syscolumns c2

on c2.id = fk.rkeyid

and c2.colid = fk.rkey

Where o1.xtype = 'F'

Order By o.name, o2.name, fk.keyno

-- Primary Key Constraints

Select

Case o1.xtype

When 'C' Then 'Check'

When 'D' Then 'Default'

When 'F' Then 'Foreign Key'

When 'PK' Then 'Primary Key'

When 'UQ' Then 'Unique'

Else 'Other' End as 'Constraint Type',

o1.name as 'Constraint Name',

o.name as 'PK Table Name',

c1.name as 'PK Column Name',

c2.name as 'FK Column Name',

o2.name as 'FK Table',

fk.keyno as 'KeyNo'

From sysobjects o

Join sysobjects o1

on o1.Parent_obj = o.id

Join sysforeignkeys fk

on fk.rkeyid = o.id

Join sysobjects o2

on o2.id = fk.fkeyid

Left Join syscolumns c1

on c1.id = fk.rkeyid

and c1.colid = fk.rkey

Left Join syscolumns c2

on c2.id = fk.rkeyid

and c2.colid = fk.rkey

Where o1.xtype = 'PK'

Order By o.name, o2.name, fk.keyno

list all triggers

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW rk_vAlltriggers

as

/*

Author :Rajani

Date :27-11-2003

Note :This view lists out all available triggers in the

current database

Use : Select * from rk_vAlltriggers

*/

select

trigger_name = name,

trigger_owner = user_name(uid),

isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),

isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),

isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),

isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),

isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger'),

object_name(parent_obj) as 'Parent Table'

from sysobjects

where type = 'TR'

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

get all tables and counts

declare @tablename varchar(255)

declare curTables cursor for

select name from sysobjects

where type='U'

order by name

declare @statement varchar(1000)

create table #temp

(tablename varchar(255),[rows] int)

open curTables

fetch next from curTables into @tablename

while @@fetch_status=0

begin

select @statement='insert into #temp select '''+@tablename+''',count(*) from '+@tablename

print @statement

exec (@statement)

fetch next from curTables into @tablename

end

close curTables

deallocate curTables

select * from #temp

drop table #temp

-- find all procedures and tables that use a particular column

If exists (Select * from sysobjects where name = 'FindObjByColumn_sp' and type = 'P')

drop proc FindObjByColumn_sp

go

Create Proc FindObjByColumn_sp @Column varchar(30)

/******************************************************************************************************

Procedure Name : FindObjByColumn_sp

Purpose : Search the database for all tables and all procedures that use a specific column.

Called by : N/A

Input Params : @Column - stores the column name that user is searchin for

Output Params : None

Return Values :

Written By : PNE 08/04/2002

Syntax : FindObjByColumn_sp 'DistributionChannel'

Change history :

********************************************************************************************************/

--SYNTAX:

AS

Declare @ssql varchar(100)

--Add wildcard so that seach can be performed even if only part of the column name is used

Select @Column = @Column + '%'

SET NOCOUNT ON

select @ssql = 'Tables that contain columns that begin with ' + @Column

select @ssql

print '----------------------------------------------------------'

SELECT convert(varchar(60), O.NAME) "Table Name"

FROM SYSCOLUMNS C, SYSOBJECTS O, master.dbo.systypes T

WHERE C.ID = O.ID AND C.xtype = T.xtype

AND C.NAME LIKE @Column

and O.Type = 'U'

group by o.name

--For stored procedures, add wildcard to beginning because column will be contained within text

Select @Column = '%' + @Column

select @ssql = 'Procs that contain the word ' + replace(@Column, '%', '')

select @ssql

print '----------------------------------------------------------'

select convert(varchar(60), O.NAME) "Proc Name"

from sysobjects o, syscomments c

where o.id = c.id

and c.text like @Column

SET NOCOUNT OFF