Today I had to write a few simple update stored procedures and a couple of update statements. This got me thinking, seeing that I’m a SQL / .Net developer, I create loads of these every month.
I don’t like doing repetitive tasks so why not spend a little time and write a script that generate these for me? So here it goes, here are two scripts and they are available as is. I know there are some data types that might not work or get excluded or throw exception etc etc etc…for the bulk of my work this will work a charm and in any case I need to check before I compile them.
Note: Your database context needs to be the same for the stored procedure/update statement as well as the table in question.
Execution for Update Statement
EXEC [GenerateUpdateStatementForTable] @TableName = 'Offices'
I needed a script to script out all the indexes with their included columns. All the scripts I could find on the inernet only scripted the normal columns, so I created my own script to script all columns and included columns.
At the moment this script does not support Fill Factor, Filters, With Options or File Groups.
If and when I make any changes I will update this script. If you like you can make changes and send them to me and I’ll update accordingly
ALTER PROC ScriptOutIndexes
SET NOCOUNT ON
Read More »
Today I had to find all procedures where two table were referenced. Also I had to Prioritise them in order to work on the most used procedure first.
After searching the web I found a few snipets of code here and there and put the following together.
Note: The query won’t return procedures that have not been executed since the last restart.
Here is part of the join clause, adapt as neccessary.
A.ROUTINE_DEFINITION LIKE ''%AccountManagement%''
A.ROUTINE_DEFINITION LIKE ''%LTCDB_admin.tbl_Sales_Contacts%''
Here is the complete queryRead More »