Creating a SQL Server Stored Procedure Generator using WPF
page 2 of 8
by Brian Mains
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 50840/ 189

System Views

SQL Server has a series of system views that can be used to query tables, columns, table relationships, stored procedures, and other information about a SQL Server database. Through these views it is possible to get the list of a table's columns and their data types, and generate a stored procedure from this information. This would assume that the parameter name for a stored procedure would be the same as the column name with the "@" sign preceding it. To get the list of tables from SQL Server 2000/2005, use the following query.

Listing 1

select * from INFORMATION_SCHEMA.Tables where Table_Type = 'BASE TABLE'

The query above uses the INFORMATION_SCHEMA views to query information about the table only. The above query only works in the context of the database it is querying from, so you have to change the database name to collect information from another database. Use the "use" statement or selecting the database in Query Analyzer or Management Studio drop down will do this.  Note that the important fields that come from this query are the TABLE_NAME and TABLE_SCHEMA columns, and the possible table type values are "BASE TABLE" and "VIEW."

To query the columns, the following query can be used:

Listing 2

select * from INFORMATION_SCHEMA.Columns

This will query information about columns, but there are two small complications. First, the TABLE_TYPE field is stored only in the Tables view, meaning an inner join is necessary when limiting the query to tables only. Secondly, the data type length is broken up into several fields.  The first field is the CHARACTER_MAXIMUM_LENGTH field, which represents the maximum length of text data. The second field is the NUMERIC_PRECISION, which is the total number of digits for the field, and the associated NUMERIC_SCALE for the total number of digits to the right of the decimal point. The other situation is with dates; dates have their own precision, which does not match their actual precision, but is a precision relative to dates.  For instance, SQL Server datetime data type is 8 bytes of storage with a precision of 23, but is represented as a precision of 3 when queried from the view.

To get the primary key information, I assume the order of the table has the primary key field first; however, in reality this information can come from the following query which matches the primary or foreign key name to the table, schema, and column that is the primary or foreign key.

Listing 3

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

View Entire Article

User Comments

Title: Good generator   
Name: Nilesh Gambhava
Date: 2011-02-09 5:09:14 AM
Comment:
Thanks Libal,

I was looking professional touch stored procedure generator and my all requirements are satisfied with tools4sql.
Title: Stored Procedure Generator   
Name: Libal
Date: 2010-06-24 10:24:55 AM
Comment:
I read your article, it sounds very interesting. But when I searched for more stored procedure generator, I found very interesting stored procedure generator named : T4S - Stored Procedure Generator found on www.tools4sql.net.

Thanks.....

Regards,
Libal
Title: Excellent stored procedure generator   
Name: Libal
Date: 2010-06-24 10:18:02 AM
Comment:
I read this article, this article is very nice. But when I searched for more stored procedure generator, I found very intersting & excellent stored procedure generator. It has rich configuration set & it is very easy to use. its name is T4S - Stored Procedure Generator & url : www.tools4sql.net
Title: Mondal   
Name: Chinmayee
Date: 2009-04-29 7:16:55 AM
Comment:
this is help ful
Title: how to get list of tables from database using vb6.0 reply   
Name: Brian
Date: 2009-03-08 2:53:20 PM
Comment:
The table_name is the field returned; in VB 6, you have to use ADO recordset to loop through the rows and get the table_name field, to get the list of the tables.
Title: how to get list of tables from database using vb6.0   
Name: Madhuri
Date: 2009-03-02 2:43:20 AM
Comment:
query1 = "select table_name as Name from INFORMATION_SCHEMA.Tables where TYPE ='S'"
wht next how to get the list of tables
Title: sql server   
Name: deepa
Date: 2008-10-22 6:45:33 AM
Comment:
it's good to way of teaching
Title: Great example   
Name: gabo
Date: 2008-09-01 3:59:25 PM
Comment:
As a matter of fact I use it all the time. Thanks.
Title: A good start   
Name: Ralph D. Wilson II
Date: 2007-09-19 10:36:05 AM
Comment:
This presents a good start toward creating Stored Procs; however, I can see several limitations in it. Perhaps the most glaring limitation is the fact that it appears to only facilitate the creation of very basic SP's and, while that is useful, I am assisting C#/ASP.Net developers in the creation of much more complex SP's.
Title: Useful   
Name: Niall
Date: 2007-08-30 3:32:49 AM
Comment:
This is interesting and I can see a very good use for this type of programming in my current project. One reservation I have though is the use of the WPF. Specifically the Presentation part of WPF.
Title: Code is Attached   
Name: Brian Mains
Date: 2007-08-26 7:26:33 PM
Comment:
The code is attached; see the downloads section.
Title: download this software   
Name: ankti
Date: 2007-08-26 4:22:23 PM
Comment:
i want to see this software






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-26 5:20:46 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search