Sunday, April 3, 2011

Automatically create all Store Procedures

I have often to write many store procedures in SQL Server!
So i decided to speed up my work, writing just 3 Store Procedures that automatically create all "Insert", "Update", "Delete" Store Procedures, of all tables, in the selected DB.

declare crs_tab cursor for
select [name] from sys.tables
where [name] not like 'sys%'

open crs_tab

As you can see, I used the "cursor" and "information_schema.columns" to get infos about all tables (and view) from the DB.

declare crs_structure cursor for
select c.[name], data_type ,ISC.character_maximum_length,ISC.character_maximum_length
from sys.columns C
join sys.tables T on t.[object_id]=c.[object_id]
join information_schema.columns ISC
on ISC.table_name=t.[name] and Column_name=c.[name]
ISC.table_name=t.[name] and Column_name=c.[name]

Download the 3 txt and create the 3 store procedures from these files.
Run the store procedures!

Then you will have all Insert, Update and Delete Store Procedures created from all Tables of your DB!

Good work and enjoy!

These Store Procedures, are only a starting point... so sometimes, if your tables are not "standard", you have to create them by hand or customize the 3 txt :)

2012 | aspnet code by Michele | don't try this at home
Mirko Iodice Notageek
Vladimir Carrer Carrer web log