SQL

Who owns what?

I had a need to find what objects in any of our databases on a server were owned by “dbauser”. The reason I needed to do this is that we expect all objects to be owned by “dbo”, and our support department (in their infinite wisdom) had created a few stored procs with the wrong owners, which caused problems when our update scripts ran.

The following SQL snippet creates a cursor to loop over all databases, then creates a SQL string to list all objects owned by “dbauser”, along with the database name and the object type. You must have adequate permissions in each database queried to run this!

declare @dbname varchar(50), @userid int
declare @sql varchar(1000)
declare curDB cursor
for select name from master..sysdatabases
open curDB
fetch next from curDB into @dbname
while @@fetch_status = 0
begin
select @sql = 'select ''' + @dbname + ''', so.name, so.xtype from '
+ @dbname + '..sysobjects so inner join '
+ @dbname + '..sysusers su on so.uid = su.uid where su.name = ''dbauser'''
exec (@sql)
fetch next from curDB into @dbname
end
close curDB
deallocate curDB


Grant Execute

When I’m in the middle of creating stored procedure after stored procedure, I find it very easy to forget to assign Execute rights to the correct user, and I never find out until I get the error thrown down to the client. This stored procedure simply calls “grant execute on [proc] to [user]” for each proc in the current database. You’ll need to be connected as DBO to get it to work, of course…

Example: GrantExecuteToUser ‘myuser’

create proc dbo.GrantExecuteToUser
@User varchar(50)
as
declare @Name varchar(256), @sql varchar(1000)
-- Create a cursor over all the Procs in the current database, by looking in sysobjects declare curProcs cursor for
select distinct Name from sysobjects where xtype = 'P' and Category = 0

open curProcs

fetch next from curProcs into @Name

while @@fetch_status = 0
begin
-- Build the SQL statement to grant the permissions
select @sql = 'grant execute on ' + @Name + ' to ' + @User
print @sql
-- Run it
exec (@sql)
fetch next from curProcs into @Name
end
close curProcs
deallocate curProcs

GO


Table from a string

With the advent of SQL Server 2000 we got a few new and very useful features, User Defined Functions being one of the ones I use the most. Okay, they don’t really allow you to do anything you couldn’t manage before, but they can be so much more elegant…

This one takes a character list of comma-separated values (e.g. ’1,2,3,4′) and turns then into a Table datatype (another of the new features). I’ve used this in cases where I’m expecting a list of options from the user interface and I want to act on all of them at once – you can use the Table datatype in a join in your stored proc. Don’t forget to call it using it’s owner (dbo, in this example)!

Example: select dbo.fnListToIntTable(’1,2,3,4′)

create function dbo.fnListToIntTable(@List varchar(1000))
returns @ret table(Item int)
as
begin
if len(@List) = 0 return

declare @item int, @pos int, @last int

-- Initialise
select @pos = 0, @last = 1
select @pos = charindex(',', @List, @pos)

while @pos > 0
begin
-- Get the next value
select @item = cast(substring(@List, @last, @pos - @last) as int)

-- Add it to the list
insert into @ret (Item) values (@item)

-- Move on
select @last = @pos + 1
select @pos = charindex(',', @List, @last)
end

-- Add the last one in the list
select @item = cast(substring(@List, @last, len(@List)) as int)
insert into @ret (Item) values (@item)

return

end


Mixed case strings

In SQL Server you can convert to upper and lower case very easily, but you can’t convert to “Proper case” easily at all. That’s what this one does. The @delimchars argument can be used to add additional delimiting characters, if needed on an ad-hoc basis. I tend to add them to the string in the code instead.

I have a feeling I based this on some sample code from somewhere, but it’s so long ago I can’t remember where.

Example: select dbo.fnProperCase(‘a sample string’, null)
Returns: ‘A Sample String’

create function dbo.fnProperCase (@instring nvarchar(1000), @delimchars nvarchar(1000))
returns nvarchar(1000)

declare @pos int
declare @outstring nvarchar(255)
declare @char char(1)

select @outstring = '', @pos = 0

if @delimchars is null
set @delimchars = ' ''-_.,()'

-- Loop through the entire string
while @pos < len(rtrim(@instring))
begin
select @pos = @pos + 1, @char = substring(@instring,@pos,1)
if @pos = 1
select @outstring = upper(@char)
else
if charindex(substring(@instring, (@pos - 1), 1), @delimchars) > 0
select @outstring = substring(@outstring,1,@pos) + upper(@char)
else
select @outstring = substring(@outstring,1,@pos) + lower(@char)
end

return @outstring

end