Below is the list of queries that I am upto with my MSSql2000DBSchemaProvider for SmartCodeGenerator. I thought this might be useful and quick reference for others too.
GetTables
string cmdText = string.Format(@"Select TABLE_NAME, TABLE_SCHEMA OWNER, REFDATE CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES , sysobjects
where Table_Name = sysobjects.[name]
and TABLE_CATALOG = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1", database.Name);
GetTableColumns
string commandText = string.Format(@"Select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, TABLE_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
from INFORMATION_SCHEMA.COLUMNS
where Table_Name = '{0}'
and Table_Schema ='{1}'
order by ORDINAL_POSITION",table.Name, table.Owner);
GetTableKeys // ForeignKeys
string commandText = string.Format(@"SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1, INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2
Where t2.TABLE_CATALOG = t1.TABLE_CATALOG
AND t2.TABLE_SCHEMA = t1.TABLE_SCHEMA
AND t2.TABLE_NAME = t1.TABLE_NAME
AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME
and t1.TABLE_Catalog = '{0}' AND t1.TABLE_NAME = '{1}'
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
order by ORDINAL_POSITION", table.Database.Name, table.Name);
Get Referential Info of a foreignkey_Constraint
string commandText1 = string.Format(@"select t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.ORDINAL_POSITION, t1.TABLE_NAME
from Information_Schema.Key_Column_Usage t1, Information_Schema.Referential_constraints t2
where t2.Constraint_name = '{0}'
and t2.Unique_Constraint_Name = t1.Constraint_Name
and table_Catalog = '{1}'
order by Ordinal_Position", fk.ConstraintName, table.Database.Name);
Get PrimaryKey Schema
string commandText = string.Format(@"SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1, INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2
Where t2.TABLE_CATALOG = t1.TABLE_CATALOG
AND t2.TABLE_SCHEMA = t1.TABLE_SCHEMA
AND t2.TABLE_NAME = t1.TABLE_NAME
AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME
and t1.TABLE_Catalog = '{0}' AND t1.TABLE_NAME = '{1}'
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
order by ORDINAL_POSITION", table.Database.Name, table.Name);
GetViews
string cmdText = string.Format(@"Select TABLE_NAME, TABLE_SCHEMA OWNER, REFDATE CREATE_TIME
FROM INFORMATION_SCHEMA.VIEWS , sysobjects
where Table_Name = sysobjects.[name]
and TABLE_CATALOG = '{0}'
and substring(VIEW_DEFINITION, 1, 1)!= 'C'
ORDER BY 1", database.Name);
GetViewText
string cmdText = string.Format(@"select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS
where Table_Name = '{0}'
and Table_catalog ='{1}'
and Table_Schema ='{2}'", view.Name, view.Database.Name, view.Owner);
GetViewColumns
string commandText = string.Format(@"Select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, TABLE_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
from INFORMATION_SCHEMA.COLUMNS
where Table_Name = '{0}'
and Table_Schema ='{1}'
order by ORDINAL_POSITION", view.Name, view.Owner);