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

Deleting records fast

Use TRUNCATE instead of DELETE on tables that do not contain any foreign keys for a faster delete. Truncate does not post to the log.
You could temporarily remove all foreign keys, then use Truncate, then add the foreign keys again I guess too.

Find SMO in Stored Procs

This is a very handy procedure that will return all Views, Stored Procs, Functions and Triggers that contain the string being searched.

I have been working on a 2005 Management Studio addin that uses this procedure and returns the SMO Nodes in a treeview, which you can then double click to open the object to edit/view. Unfortunately it can't be installed into Query Analyser. I am hoping to be able to provide the code for this sometime soon.

-- Set the @searchtext param to the name of the object that you want
-- to find within code of a stored proc.
-- It performs a partial match on the search text by using the % property pre and post
DECLARE @searchtext varchar(50) -- the search text
DECLARE @sql varchar(256) --Building the SQL to run
DECLARE @SearchAllDBs bit --1 = Search All Databases, 0 = Search Current DB

-- Set this
SET @searchtext = ''
SET @SearchAllDBs = 1
if @SearchAllDBs = 0
begin
set @sql = 'SELECT name, type ' set @sql = @sql +
'FROM sysobjects a INNER JOIN syscomments b on a.id = b.id '
end
else
begin
set @sql = 'SELECT ''?'' ''Database'', name, type ' set @sql = @sql +
'FROM ?.dbo.sysobjects a INNER JOIN ?.dbo.syscomments b on a.id = b.id '
end

set @sql = @sql + 'WHERE type in (''U'',''IF'', ''P'', ''V'', ''FN'', ''TF'', ''TR'') '+
'AND b.text LIKE ''%' + @searchtext + '%'' '+
'ORDER BY type, name'

if @SearchAllDBs = 0
exec (@sql)
else
exec sp_MSforeachdb @sql

Performance Tuning Stored Procedures

Naming Conventions
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name. Important: If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
Schema prefixing
Always reference your database objects using the schema name as a prefix. For the same reason as in the Naming Conventions tip, when looking for a database object for the first time, SQL Server will look in a specific order. But by prefixing the database object in question, it can find it straight away.
e.g.
Select name from tableA where Id = 2
Becomes,
Select name from dbo.tableA where Id = 2
Using database functions become,
Select dbo._fn_GetName(@ID, dbo.tableA)
Using joins become,
Select Id, Name, Address From dbo.Customer Left Outer Join dbo.Address on Customer.Id = Address.Id

Sql Links

Like I said in my first blog, this is mainly for me to store related information and links etc so there won't be too much in each blog.
Dynamic Search Conditions in T-SQL - As the title suggests, this link has helpful info on Search conditions in T-SQL.
Online Sql Magazine - Although I don't subscribe to this, there are a lot of free articles.
Arrays and Lists in SQL Server 2005 - This is a good link dealing with different T-SQL coding samples, including my favour, the Table of Numbers.