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
