@Rahul The Dba

A primary key is always a good idea, as it enforces uniqueness. This doesn't have to be an identity value though and I have tables, especially ones with ISO Codes in, where the code is the primary key. I realise that my code only works on identity primary keys but to deal with all forms of tables would be a much bigger task. I chose the most common type of table so that the code was useful to the most people.

@hardikce.08

A practical example: ok, so I design a new database with 40 tables in, 30 of which are tables with a single identity primary key, I run the code and within seconds I have 90 new stored procedures which I didn't have to write manually. This both saves time and also avoids mistakes, as the column names and data types are read from the system tables and so will always be correct. Also you may have a corporate policy that say denies access to base tables and states that all data access must be through views - no problem, just change the where clauses in the code to get rid of the one primary key identity columns restriction and write a new stored procedure to create the views. You can then rerun this periodically to pick up new columns and names changes etc.

@Kristian Ask

I realise that this is quite limiting Kristian but, as I say, I had to shoot for a certain type of table in the article, so I chose the most common type (in my experience).

Folks, the article is intended to show you what can be achieved with simple automation code, it was not intended to be instantly useable on all your databases, as they are all different and we will all have different architectures. You may for instance not have sperate insert and update procedures but one "save" or "set" procedure, in which case you will need to adjust the code accordingly, plus we all have corporate standards to adhere to. The article is intended to give an example that you can use as a starting point for doing your own automation. You will never be able to cover every table design with automation code as it simply isn't possible but, if you can automate 60%, that gives you more time to concentrate on the fun stuff. The tradeoff is this: you could spend a long time coding the automation code to handle every eventuality and still never get it 100% correct so surely it is better to automate the simple and dull tasks on certain types of tables, giving you more time for the complex and interesting ones that still have to be done manually.

Cheers, James

P.S. PLEASE DO NOT RUN THIS IN ANY DATABASE THAT HAS THE SAME STORED PROCEDURE NAMING CONVENTIONS, AS YOUR EXISTING PROCEDURES WILL BE DROPPED AND RECREATED!!! ADVENTUREWORKS IS ALWAYS A SAFE PLACE TO TEST.