Yet another SQL Script you might find useful.
This one I store in the master database so I have sp_ as the prefix. Normally my standard for stored procedures would be to prefix them with proc_.
This one I use to mimic the sp_MSForEachTable proc but this one is to manage permission on stored procedures within any database.
As always... I hope you find it of as much use as I do. I use it daily!
/*******************************************************************************************************
* master.dbo.sp_AOCForEachRoutine
* Creator: Merrill Nelson
* mnelson@courts.az.gov
*
* Description: Execute a Command on Procedures and/or Functions
* Notes: Strings/Parameters: @Action - (ie: 'grant exec')
* ,@Role - Database Role to Apply to
* ,@Filter1 - (ie: 'proc_%')
* ,@Filter2 - (ie: 'func_%')
*
* Usage:
* exec sp_AOCForEachRoutine 'grant execute', 'Developer', 'proc_%'
* exec sp_AOCForEachRoutine 'grant execute', 'Developer', 'func_%'
* exec sp_AOCForEachRoutine 'grant view definition', 'Developer', 'proc_%', 'func_%'
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* Merrill Nelson 07/07/06 Original Creation
********************************************************************************************************/
CREATE Procedure [dbo].[sp_AOCForEachRoutine]
@Action varchar(25)
,@Role varchar(15)
,@Filter1 varchar(10) = ''
,@Filter2 varchar(10) = ''
As
-- Create Temp Table to hold Routine name info
Create table #Routines ( [ID] int identity(1,1), Routine_Schema varchar(20), Routine_Name varchar(75) )
-- Setup local variables
Declare @Count int
,@SQL varchar(200)
,@Schema varchar(20)
,@RoutineName varchar(75)
,@MySQL varchar(1000)
-- Setup SQL to load temp table
Set @MySQL = '
Select Routine_Schema, Routine_Name
From ' + db_name() + '.information_Schema.routines
Where Routine_Name like ''' + @Filter1 + '''
Or Routine_Name like ''' + @Filter2 + ''' '
-- Load Temp Table
Insert into #Routines
Exec(@MySQL)
-- Set Counter variable to greatest [ID] from Temp Table
Set @Count = ( Select max([ID]) From #Routines )
-- While Counter is > 0
While @Count > 0
Begin
-- Get Schema Name from Temp Table
Set @Schema = ( Select Routine_Schema From #Routines Where [ID] = @Count )
-- Get Routine Name from Temp Table
Set @RoutineName = ( Select Routine_Name From #Routines Where [ID] = @Count )
-- Set SQL statement to run using current db name, schema, routine, and role
Set @SQL = @Action + ' on ' + db_name() + '.' + @Schema + '.' + @RoutineName + ' to ' + @Role
-- Print command to be run
Print @Action + ' on ' + db_name() + '.' + @Schema + '.' + @RoutineName + ' to ' + @Role
-- Execute Command
Exec(@SQL)
-- Decrement Counter variable
Set @Count = @Count -1
-- Loop
End
Drop table #Routines
Regards,
-Merrill
Friday, July 25, 2008
SQL Server - Scripts and SPs
Here is a script I came up with several months ago from bits and pieces I picked up here and there. I created a stored procedure with it because it is something that I will run many times to make sure that I have all the indexes I should have on all my databases.
/*******************************************************************************************************
* dbo.proc_BuildMissingFKIndexes
* Creator: Merrill Nelson
*
* Description: Create Indexes for ALL FKs where Index is Missing
* Notes: Executes and prints SQL statement
*
* Usage (Example):
* Exec dbo.proc_BuildMissingFKIndexes
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* Merrill Nelson 04/04/08 Original Creation
********************************************************************************************************/
Create Procedure [dbo].[proc_BuildMissingFKIndexes]
AS
Declare @WorkTable Table(RID Int Identity(1,1), Query varchar(1000))
Declare @SQL varchar(1000)
,@Counter Int
Insert Into @WorkTable
Select 'If Not Exists (Select 1
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where I.[object_id] = ' + convert(varchar(25),FK.parent_object_id) + '
And I.index_id = IC.index_id
And IC.column_id = ' + convert(varchar(25),FKC.parent_column_id) + '
And objectpropertyex(I.[object_id],''ismsshipped'') = 0
And I.is_hypothetical = 0)
Begin
Create Index FKX_' + (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '_'
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.[parent_object_id]
And SC.column_id = FKC.parent_column_id) + ' on '
+ (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '('
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.parent_object_id
And SC.column_id = FKC.parent_column_id) + ')
End'
From sys.foreign_keys FK
Inner Join sys.foreign_key_columns FKC
On FK.[object_id] = FKC.constraint_object_id
Where Not Exists (Select 'an index with same columns and column order'
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where FK.parent_object_id = I.[object_id]
And I.index_id = IC.index_id
-- And FKC.constraint_column_id = IC.key_ordinal
And FKC.parent_column_id = IC.column_id
And objectpropertyex(I.[object_id],'ismsshipped') = 0
And I.is_hypothetical = 0)
And FK.is_ms_shipped = 0
And FK.[name] Not Like 'FK_Audit%'
Order By FK.parent_object_id
Set @Counter = 1
While @Counter <= (Select Max(RID)
From @WorkTable)
Begin
Set @SQL = (Select Query
From @WorkTable
Where RID = @Counter)
Set @Counter = @Counter + 1
Print @SQL
Exec(@SQL)
End
Format it so that it meets your standards and Enjoy!
- Merrill
/*******************************************************************************************************
* dbo.proc_BuildMissingFKIndexes
* Creator: Merrill Nelson
*
* Description: Create Indexes for ALL FKs where Index is Missing
* Notes: Executes and prints SQL statement
*
* Usage (Example):
* Exec dbo.proc_BuildMissingFKIndexes
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* Merrill Nelson 04/04/08 Original Creation
********************************************************************************************************/
Create Procedure [dbo].[proc_BuildMissingFKIndexes]
AS
Declare @WorkTable Table(RID Int Identity(1,1), Query varchar(1000))
Declare @SQL varchar(1000)
,@Counter Int
Insert Into @WorkTable
Select 'If Not Exists (Select 1
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where I.[object_id] = ' + convert(varchar(25),FK.parent_object_id) + '
And I.index_id = IC.index_id
And IC.column_id = ' + convert(varchar(25),FKC.parent_column_id) + '
And objectpropertyex(I.[object_id],''ismsshipped'') = 0
And I.is_hypothetical = 0)
Begin
Create Index FKX_' + (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '_'
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.[parent_object_id]
And SC.column_id = FKC.parent_column_id) + ' on '
+ (Select SO.[name]
From sys.objects SO
Where SO.[object_id] = FKC.parent_object_id) + '('
+ (Select SC.[name]
From sys.objects SO
Inner Join sys.columns SC
On SC.[object_id] = SO.[object_id]
Where SO.Type = 'U'
And SO.[object_id] = FKC.parent_object_id
And SC.column_id = FKC.parent_column_id) + ')
End'
From sys.foreign_keys FK
Inner Join sys.foreign_key_columns FKC
On FK.[object_id] = FKC.constraint_object_id
Where Not Exists (Select 'an index with same columns and column order'
From sys.indexes I
Inner Join sys.index_columns IC
On I.[object_id] = IC.[object_id]
Where FK.parent_object_id = I.[object_id]
And I.index_id = IC.index_id
-- And FKC.constraint_column_id = IC.key_ordinal
And FKC.parent_column_id = IC.column_id
And objectpropertyex(I.[object_id],'ismsshipped') = 0
And I.is_hypothetical = 0)
And FK.is_ms_shipped = 0
And FK.[name] Not Like 'FK_Audit%'
Order By FK.parent_object_id
Set @Counter = 1
While @Counter <= (Select Max(RID)
From @WorkTable)
Begin
Set @SQL = (Select Query
From @WorkTable
Where RID = @Counter)
Set @Counter = @Counter + 1
Print @SQL
Exec(@SQL)
End
Format it so that it meets your standards and Enjoy!
- Merrill
Instant Software Generator
Creates Instant Software Products That you Can Sell And Keep 100% Of The Profits In 30 Minutes - Without Programming!
Now that is something we could all use!
Now that is something we could all use!
The Silver Lining
Squidoo has hit the internet with a bang and is at the forefront of the Web 2.0 revolution. It is the easiest, most user friendly way of getting your business up and running on the web, not least because of the zero cost. WEB 2.0 moves fast and you need to keep ahead of the market - be ready when the next Niche Market unfolds, get there ahead of the competition but research takes a long time, often out of date by the time you've got the info you need. I can do my research at the same time as boosting my traffic and rankings using The Silver Lining Method, so can you with just a few minutes concentrated effort.
Software Designer Pro
Here's How You Can Instantly Gain an Almost Unstoppable Edge On Your Competition!
Google SEO Magic
All the best,
If you continue on the same path, will the kind of successes you've been looking for be within your reach? Or will you continue, stuck in a rut, with the same
mediocre Traffic Building and lifestyle?
I can't promise you will get a Million of free visitors using our system. I don't know you... Your background... Your commitment... Or your motivation.
But, we've already helped 'real people' like the ones you will read about
with our Google SEO Magic System.
I will prove that it's within your reach to make your investment back (and then
some) if you have the desire and motivation to do so. Use our videos... Test drive
some of tools and strategies we share with you... And monitor your results.
If you're not convinced that our system will help you create a wildly Google page rank within 24hrs , then just ask for your money back.
So whether or not you get high ranking with our Google SEO Magic system. This all
comes now completely up to you...
If you continue on the same path, will the kind of successes you've been looking for be within your reach? Or will you continue, stuck in a rut, with the same
mediocre Traffic Building and lifestyle?
I can't promise you will get a Million of free visitors using our system. I don't know you... Your background... Your commitment... Or your motivation.
But, we've already helped 'real people' like the ones you will read about
with our Google SEO Magic System.
I will prove that it's within your reach to make your investment back (and then
some) if you have the desire and motivation to do so. Use our videos... Test drive
some of tools and strategies we share with you... And monitor your results.
If you're not convinced that our system will help you create a wildly Google page rank within 24hrs , then just ask for your money back.
So whether or not you get high ranking with our Google SEO Magic system. This all
comes now completely up to you...
Online Survey Tools
What If Companies Such As Nike, Walmart, Fedex, Ebay, Amazon And Many Other Big Corporations would Pay You $5000+ Every Single Month Just For Your Opinions? Give Me Two Minutes And I Will Show YOU How To Sign Up With LITERALLY Hundreds Of Companies And Give Your Honest Opinion In Exchange For Large Checks Sent Straight To Your House.
SQL Server - DBA Stuff
I've been a SQL Server DBA now for the past 8 years and I'm truely tired of DBAs of other technologies not taking the SQL Server DBA serious. Just a little per peeve of mine as it sometimes makes me feel the pain like that of a minority group. Anyway, with that out of the way, I'd like to begin this blogging thing with talks about SQL Server and the daily work and issues that a SQL Server DBA faces. I'll start with the one thing that I wish was much easier to do than I have found it to be. Scripting changes to a database so that the script will run smoothly each and every time it runs. I have used tools like database modeling tools or SQL Source Safe tools that produce script by which they claim, you can use to modify your database. Everytime I have run them I can be assured that they will fail in at least one and more likely many areas. I then have to run the script against a copy of the database that I originally wanted to change so that I can work out all the bugs or kinks before I have a clean enough copy of the script to run on the database that I really want to run it on. Any SQL DBAs out there that might like to talk about their experiences with this?
- Merrill
- Merrill
Subscribe to:
Posts (Atom)