Column collation to Server Default

The following script will help you to change all columns in your database to server default collation. You can modify this script to do all your databases on particular server.

set nocount on
declare @AlterId int
declare @serverColl varchar(100)
declare @AlterString varchar(300)
set @serverColl = convert(sysname, serverproperty('collation'))
declare @Testing table
(AlterId int identity(1,1) not null primary key clustered,
AlterString varchar(300))

INSERT INTO @Testing(AlterString)
select 'ALTER TABLE [' + so.name + '] ' +
'ALTER COLUMN [' + sc.name + '] ' +
st.name +
'(' + cast(sc.length as varchar) + ') ' +
'COLLATE '+ @serverColl + ''
from syscolumns sc
join sysobjects so
on so.id = sc.id
join systypes st
on st.xtype = sc.xtype
where sc.collation is not null
and OBJECTPROPERTY(Object_id(so.name), 'IsTable') = 1
and st.name!= 'sysname'
and sc.collation not like @serverColl
select @AlterId = Max(AlterId) from @Testing

WHILE @AlterId IS NOT NULL
BEGIN
SELECT @AlterString = AlterString FROM @Testing
WHERE AlterId = @AlterId
EXEC (@AlterString)
SELECT @AlterId = Max(AlterId) from @Testing
WHERE AlterId < @AlterId
END

Renaming Database server

You may receive error 14274 - cannot update or delete job (or its steps or schedules) that originated from an MSX server. You can run the following script on MSDB database to correct:
update sysjobs
set originating_server = 'new serverName'

Stored procedure tips

Have come across this Stored procedures in SQL Server: A dozen must-have tips. From tuning to how to perform cross tab queries. You can find by clicking this link Stored procedure tips

Best practice analyser

I have just downloaded and played with the new SQL server 2005 best practice analyser (CTP). First impression is that much better than previous version. Will post more details in near future. For now you can download it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en and play with it

All tables with identity

I thought the following script is useful if you want to find out all tables with identity columns in a specific database.
use
go
SELECT
DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'