Deleting a user from all db's

If you need to recursively remove a user from all db's, run this.
What happens is the statement first checks whether the user is in the 'current' database (remember msforeachdb)
then, changes the current context to the database, executes the dropuser command, then prints that it did it.

DECLARE @usr sysname
DECLARE @passwd varchar(20)
SET @usr = 'AUser'
SET @passwd = 'password'


DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'if exists (select name from ?.dbo.sysusers where upper(name) = '''+@usr+''') '+
'begin '+
'use ? '+
'exec sp_dropuser ''' +@usr+ ''''+
'print ''dropped user from ?'''+
'end'
exec sp_msforeachdb @cmd1

Using a CSV array as a filter in a Stored Procedure

Here's a really handy trick I picked up.
If you have a stored procedure and you would like to pass a set of values in as part of a filter, ie. A list of Identifiers, or Names that you want to filter on, then you can use a handy table that contains just a bunch of numbers. I'll show you.

-- Create the table
CREATE TABLE [dbo].[Nums](
[n] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[n] ASC
) ON [PRIMARY]
) ON [PRIMARY]

-- Now add the numbers
DECLARE @index int
SET @index = 1
WHILE (@index < @NumbersToAdd)
BEGIN
INSERT INTO [dbo].[Nums] (n)
VALUES (@index)
SET @index = @index +1
END

create a function to put the values into a table.

CREATE FUNCTION dbo.ufn_CommaValues(@List varchar(8000))
RETURNS @Array Table
(
pos integer,
val varchar(10)
)
AS
BEGIN
INSERT @Array
SELECT
n - LEN(REPLACE(LEFT(@arr, n), ',', '')) + 1 AS pos,
CAST(SUBSTRING(@arr, n, CHARINDEX(',', @arr + ',', n) - n) AS INT) AS val
FROM Nums --Uses the Nums table.
WHERE n <= LEN(@arr)
AND SUBSTRING(',' + @arr, n, 1) = ','
ORDER BY pos
RETURN
END

Now we have everything you can use it like this:

CREATE PROC dbo.usp_Example
@List varchar(8000) --This is a list of comma separated GUIDS
AS
SELECT *
FROM TableA
INNER JOIN dbo.ufn_CommaValues(@List) CSV ON TableA.Id = CSV.Val


Pretty useful if you have a list of records that you want to filter on, particularly for debugging.

Using a CSV/TXT as a Table

Found a great way to use a csv file as a table in SQL

OpenRowset(
'MSDASQL'
, 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=;'
, 'select * from []'
)


You can load this into a table or use it directly. Just ensure the first line in the csv/txt is the name of the columns then you can also use them in SQL.

Very handy

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

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.