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