Moving tempdb

To move the data files for tempdb use the following:


USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'path_and_filename.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'path_and_filename.ldf >')
GO

Find all Columns in Indexes

This proc will list all the indexes and the columns that are in the indexes.
Yes you can view this from the Table object but this is a little quicker.

select
a1.name TableName
, b1.name IndexName
, d1.name ColumnName
from sysobjects a1
inner join sysindexes b1
on a1.id = b1.id
inner join sysindexkeys c1
on a1.id = c1.id
and b1.indid = c1.indid
inner join syscolumns d1
on a1.id = d1.id
and c1.colid = d1.colid
where b1.name not like '_WA%' --Remove statistics indexes
--and a1.name like '' --To specify a table
order by a1.name, b1.name

Find UnIndexed Foreign Keys

The following script will find all the foreignkeys on tables that are not currently apart of an Index for the table. This can be used to identify possible additional indexes that may need to be added to help with performance. Not all FKs need to be in an index, but it may point out some that really should be.

select
a1.name TableName
, a2.name FKName
, c1.name ColumnName
from sysobjects a1
inner join sysforeignkeys b1
on a1.id = b1.fkeyid
inner join sysobjects a2
on a2.id = b1.constid
inner join syscolumns c1
on c1.id = a1.id
and c1.colid = b1.fkey
where not exists (
select *
from sysobjects a
inner join sysindexes b
on b.id = a.id
inner join sysindexkeys k
on k.id= a.id
and k.indid = b.indid
inner join syscolumns c
on c.id = a.id
and c.colid = k.colid
where a.name not like 'sys%'
and b.name not like '_WA%'
and c.colid = b1.fkey
and c.id = a1.id
)
order by
a1.name
, a2.name
, c1.name

Backup / Restore DB using USB (or mapped) drives

I've been trying to use an external USB harddrive to restore db's from but was having problems getting Enterprise Manager to see teh drive.

Turns out the problem is with permissions / security on the drive and the SQL Server Service logon account.

Backup and Restore is handled by the MSSQLServer service and therefore all permissions relate to the logon for this service. You need to ensure that the drive/mapping has permissions / security settings that enable this user to access it.

In my case it was just giving the users role full access (probably overkill) to the drive, then it appeared in the Restore list of drives.

Counting all records in all tables

select
sysobjects.name
, sysindexes.rowcnt
from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where sysindexes.indid in (0, 1)
and objectproperty(sysobjects.id, 'IsuserTable') = 1 --Only user tables
order by 1


Heres an updated one that also returns the size used


SELECT a.Name
, SUM(b.RowCnt) EstRowCount
, SUM(b.Used) * 8 TableSize_KB
, (SUM(b.Used) * 8.0)/1024 TableSize_MB
FROM sysobjects a
LEFT OUTER JOIN sysindexes b
ON a.ID = b.ID
WHERE a.xtype IN ('U', 'S')
GROUP BY a.Name
ORDER BY SUM(b.RowCnt) desc ,a.Name