Finding the largest table in a database

A good article with SQL script for calculating the largest SQL tables inside a database including the size occupied by the indexes:
http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database



/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables

Search for a text string in a database's T-SQL

I believe the original author is Scott Burnell.


declare @textToSearchFor nvarchar(200)
declare @objectTypeToSearch char(1)
set @textToSearchFor = 'dimManagerCode'
-- use this as a sproc parameter
set @objectTypeToSearch = 'a'
-- use this as a sproc parameter (t=table,p=procedure,v=views,f=functions,a=all)
-- procedures
if (@objectTypeToSearch in ('a', 'p'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ProcName --objectName.'
from
sys.procedures p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like '%' + @textToSearchFor + '%'
order by
ProcName
end
-- tables
if (@objectTypeToSearch in ('a', 't'))
begin
select
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as TableName --objectName
from
INFORMATION_SCHEMA.COLUMNS c
where
c.COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
TableName
end
-- views
if (@objectTypeToSearch in ('a', 'v'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ViewNameDefinition --objectName
from
sys.views p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like N'%' + @textToSearchFor + N'%'
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as ViewNameOutputColumn --objectName
from
sys.all_columns c
inner join sys.objects obj
on c.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'v'
and c.[name] like '%' + @textToSearchFor + '%'
select distinct
'[' + VIEW_SCHEMA + '].[' + VIEW_NAME + ']' as ViewName,
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'as UsesTableColumn
from
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
where
COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
ViewName
end
-- functions
if (@objectTypeToSearch in ('a', 'f'))
begin
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as FunctionName --objectName
from
sys.objects obj
inner join sys.sql_modules m
on obj.object_id = m.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'FN'
and m.definition like '%' + @textToSearchFor + '%'
order by
FunctionName
end

Stored procedure for truncating SQL 2005 Logs and shrinking the database

create proc [dbo].[uspShrinkDatabase]
as
DECLARE @LogFileName sysname
DECLARE @DataFileName sysname
DECLARE @CatalogName sysname
set @CatalogName = db_name()
SELECT
@LogFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'LOG'
SELECT
@DataFileName = rtrim([name])
from
sys.database_files
where
type_desc = 'rows'
select
@LogFileName as LogFileName,
@DataFileName as DataFileName
Checkpoint;
Backup LOG @CatalogName with Truncate_Only;
DBCC SHRINKFILE(@LogFileName, 0, TRUNCATEONLY);
DBCC SHRINKDATABASE(@CatalogName);