Geeks With Blogs

News Awarded Microsoft MVP C#.NET - 2007, 2008 and 2009


I am born in Bangladesh and currently live in Melbourne, Australia. I am a Microsoft Certified Application Developer MCAD Chartered Member (C# .Net)and born in Bangladesh.
I am founder and Chief Executive Officer of
Simplexhub, a highly experienced software development company based in Melbourne Australia and Dhaka, Bangladesh. Co-founder and core developer of Pageflakes www.pageflakes.com.
Simplexhub, is on its mission to build a smart virtual community in Bangladesh and recently launched beta realestatebazaar.com.bd an ASP.NET MVC application written in C#.NET.


Some of My Articles
Flexible and Plugin based .Net Application..
Mass Emailing Functionality with C#, .NET 2.0, and Microsoft® SQL Server 2005 Service Broker'
Write your own Code Generator or Template Engine in .NET
Shahed Khan blog

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);

Posted on Wednesday, January 3, 2007 5:55 AM | Back to top


Comments on this post: MS SQL 2000 Schema Discovery Queries

# re: MS SQL 2000 Schema Discovery Queries
Requesting Gravatar...
Hi Shahed

Thanks for sharing this info! It was very helpful.

Regards
Michael
Left by Michael Bürgi on Sep 10, 2008 6:09 AM

Your comment:
 (will show your gravatar)


Copyright © Shahed Khan | Powered by: GeeksWithBlogs.net