SQL Server - Tips and more

I have been a great fan of MS SQL Server, I just wanted to share some of the tips and tricks i picked up that can be quite helpful.

1. Changing owner of tables/ procedure or Function.

For Tables

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old =
'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT
NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND
TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'',
''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

For Procedures

DECLARE @oldOwner sysname, @newOwner sysname

SELECT
@oldOwner =
'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'

select 'EXECUTE
sp_changeobjectowner
'''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE =
'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)),
'IsMSShipped') = 0

For Functions

DECLARE @oldOwner sysname, @newOwner sysname

SELECT
@oldOwner =
'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'

select 'EXECUTE
sp_changeobjectowner
'''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE =
'Function'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)),
'IsMSShipped') = 0

2. Insert Script for SQL Server

Browsing the net i found this great script it creates a procedure with takes table name as input and returns a insert script for table.

****** Object: StoredProcedure [dbo].[sp_CreateDataLoadScript] Script Date:
09/19/2008 20:53:38 ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO

Create Procedure
[dbo].[sp_CreateDataLoadScript]
@TblName varchar(128)
as
/*
exec
sp_CreateDataLoadScript 'MyTable'
*/


create table #a (id int
identity (1,1), ColType int, ColName varchar(128))

insert #a (ColType,
ColName)
select case when DATA_TYPE like '%char%' then 1 else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME =
@TblName
order by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,
@maxid int ,
@cmd1
varchar(7000) ,
@cmd2 varchar(7000)

select @id = 0 ,
@maxid =
max(id)
from #a

select @cmd1 = 'select '' insert ' + @TblName + ' (
'
select @cmd2 = ' + '' select '' + '
while @id < @maxid begin select
@id = min(id) from #a where id > @id

select @cmd1 = @cmd1 + ColName +
','
from #a
where id = @id

select @cmd2 = @cmd2
+ ' case
when ' + ColName + ' is null '
+ ' then ''null'' '
+ ' else '
+ case
when ColType = 1 then ''''''''' + ' + ColName + ' + ''''''''' else
'convert(varchar(20),' + ColName + ')' end
+ ' end + '','' + '
from #a
where id = @id
end


select @cmd1 = left(@cmd1,len(@cmd1)-1) +
' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName

select '/*' + @cmd1 + @cmd2 + '*/'

exec (@cmd1 + @cmd2)
drop
table #a

Note: you will have to turn of identity column if you want to insert the primary key as well.