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