Generate SQL Update Statement And Stored Procedure From Table Name

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'

Read More »

Script All Indexes SQL Server 2005/2008

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
AS 

 SET NOCOUNT ON 

 SELECT
 OBJECT_NAME([ixs].[object_id]) 'table_name'
 ,[ixs].[type] 'index_type'
 ,[ixs].[type_desc]
 ,[ixs].[name] 'index_name'
 ,[ixs].[is_unique]
 ,[ixs].[fill_factor]
 ,[ix_col].[key_ordinal]
 ,[col].[name] 'column_name'
 ,[ix_col].[is_descending_key]
 ,[is_included_column]

Read More »

Get all stored procedures that contain specific text in SQL Server

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%''

OR

A.ROUTINE_DEFINITION LIKE  ''%LTCDB_admin.tbl_Sales_Contacts%''

Here is the complete queryRead More »