|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
Hi;
I've looked and can't find this but I am guessing I just missed it somehow. Using an ODBC.NET connection how can I: 1) Get a list of all tables in that database? 2) Get a list of all views in that database? 3) For a given table or view, get a list of all columns in that table/view? 4) Get a list of all stored procedures in that database? I assume this is doable as ODBC is very mature. But I can't find a database independent way to do this. (I did find it in OLEDB.NET). -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm Ah, ADO.NET does expose a GetSchema from the Connection object--the question
is, does the Odbc.Net data provider implement it. Frankly, I doubt it. That's the price of using an OSFA provider. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) Between now and Nov. 6th 2006 you can sign up for a substantial discount. Look for the "Early Bird" discount checkbox on the registration form... ----------------------------------------------------------------------------------------------------------------------- "David Thielen" <thielen@nospam.nospam> wrote in message news:B9C1B289-15AD-4BD6-9720-9030A346A1D9@microsoft.com... > Hi; > > I've looked and can't find this but I am guessing I just missed it > somehow. > Using an ODBC.NET connection how can I: > 1) Get a list of all tables in that database? > 2) Get a list of all views in that database? > 3) For a given table or view, get a list of all columns in that > table/view? > 4) Get a list of all stored procedures in that database? > > I assume this is doable as ODBC is very mature. But I can't find a > database > independent way to do this. (I did find it in OLEDB.NET). > > -- > thanks - dave > david_at_windward_dot_net > http://www.windwardreports.com > > Cubicle Wars - http://www.windwardreports.com/film.htm > > "David Thielen" <thielen@nospam.nospam> wrote in message It will depend on the ODBC driver for the particular RDBMS - which RDBMS is news:B9C1B289-15AD-4BD6-9720-9030A346A1D9@microsoft.com... > I assume this is doable as ODBC is very mature. But I can't find a > database > independent way to do this. (I did find it in OLEDB.NET). it, AAMOI...? That's the problem - we're a 3rd party library so if we support ODBC.NET then
we have to have this capability for any database it connects to. -- Show quotethanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Mark Rae" wrote: > "David Thielen" <thielen@nospam.nospam> wrote in message > news:B9C1B289-15AD-4BD6-9720-9030A346A1D9@microsoft.com... > > > I assume this is doable as ODBC is very mature. But I can't find a > > database > > independent way to do this. (I did find it in OLEDB.NET). > > It will depend on the ODBC driver for the particular RDBMS - which RDBMS is > it, AAMOI...? > > > Hi Dave,
The ODBC.NET itself support getting the schema information by using OdbcConnection.GetSchema method. It can meet all the requirements here. However, whether a database supports this depends on the database itself and the driver that vendor has provided. In this case, we cannot say for sure that we can do or cannot this. It is out of the scope that our app can reach. If anything is unclear, please feel free to let me know. Kevin Yu Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.) Hi;
Ok, I have it but with 2 things I would like to get better. First, to get tables or views you can do: using (OdbcConnection sc = new OdbcConnection(connStr)) { sc.Open(); DataTable schemaTable = sc.GetSchema("tables"); // or views for (int ind = 0; ind < schemaTable.Rows.Count; ind++) if (((string)(schemaTable.Rows[ind])["TABLE_TYPE"]).ToUpper() == "TABLE") System.Console.WriteLine(" " + (string)(schemaTable.Rows[ind])["TABLE_NAME"]); } I tried using restrictions to get just non-system tables but everything I tried gave me back no tables. For stored procedures you can do: using (OdbcConnection sc = new OdbcConnection(connStr)) { sc.Open(); DataTable schemaTable = sc.GetSchema("procedures"); for (int ind = 0; ind < schemaTable.Rows.Count; ind++) System.Console.WriteLine(" " + (string)(schemaTable.Rows[ind])["PROCEDURE_NAME"]); } And for these I have found no way to get just user-defined ones. The returned data has the same type for all of them. Any ideas? -- Show quotethanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Kevin Yu [MSFT]" wrote: > Hi Dave, > > The ODBC.NET itself support getting the schema information by using > OdbcConnection.GetSchema method. It can meet all the requirements here. > However, whether a database supports this depends on the database itself > and the driver that vendor has provided. In this case, we cannot say for > sure that we can do or cannot this. It is out of the scope that our app can > reach. > > If anything is unclear, please feel free to let me know. > > Kevin Yu > Microsoft Online Community Support > > ================================================== > Get notification to my posts through email? Please refer to > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif > ications. > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues > where an initial response from the community or a Microsoft Support > Engineer within 1 business day is acceptable. Please note that each follow > up response may take approximately 2 business days as the support > professional working with you may need further investigation to reach the > most efficient resolution. The offering is not appropriate for situations > that require urgent, real-time or phone-based interactions or complex > project analysis and dump analysis issues. Issues of this nature are best > handled working with a dedicated Microsoft Support Engineer by contacting > Microsoft Customer Support Services (CSS) at > http://msdn.microsoft.com/subscriptions/support/default.aspx. > ================================================== > > (This posting is provided "AS IS", with no warranties, and confers no > rights.) > > Hi Dave,
1. The schema information on Tables only have 3 restrictions. They are database, owner, and table name. So we cannot filter the user tables with GetSchema method. In this case, the simplest way to to create a DataView object on the schemaTable. Here is an example: DataView dv = new DataView(schemaTable); dv.RowFilter = "TABLE_TYPE='TABLE'"; Then dv will show user tables only. 2. The GetSchema("procedures") on SQL Server is actually calling sp_stored_procedures to get all the procedures back. The returned table from sp_stored_procedures didn't include an identifier to show if this stored procedure is a system procedure or user one. In this case, we are unable to know whether it is a system procedure. It seems to be a limitation. Sorry for the inconvenience. Kevin Yu Microsoft Online Community Support ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.) That's what I was afraid of.
What is weird is the returned schemas all have the same type in their row data. You would think that the type would show system vs user. But - with this info we can add ODBC to our AutoTag program and that is the key part. -- Show quotethanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Kevin Yu [MSFT]" wrote: > Hi Dave, > > 1. The schema information on Tables only have 3 restrictions. They are > database, owner, and table name. So we cannot filter the user tables with > GetSchema method. In this case, the simplest way to to create a DataView > object on the schemaTable. Here is an example: > > DataView dv = new DataView(schemaTable); > dv.RowFilter = "TABLE_TYPE='TABLE'"; > > Then dv will show user tables only. > > 2. The GetSchema("procedures") on SQL Server is actually calling > sp_stored_procedures to get all the procedures back. The returned table > from sp_stored_procedures didn't include an identifier to show if this > stored procedure is a system procedure or user one. In this case, we are > unable to know whether it is a system procedure. > > It seems to be a limitation. Sorry for the inconvenience. > > Kevin Yu > Microsoft Online Community Support > ================================================== > > (This posting is provided "AS IS", with no warranties, and confers no > rights.) > > Hi Dave
The PROCEDURE_TYPE column is not used to indicate whether this procedure is a user one or system one. This value can be one of the following: 0 = SQL_PT_UNKNOWN 1 = SQL_PT_PROCEDURE 2 = SQL_PT_FUNCTION However, according to the document, SQL Server always returns 2.0. Kevin Yu Microsoft Online Community Support ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.) Please also check the following link:
http://msdn2.microsoft.com/en-us/library/ms190504.aspx Kevin Yu Microsoft Online Community Support ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.) That's what I was afraid of.
What is weird is the returned schemas all have the same type in their row data. You would think that the type would show system vs user. But - with this info we can add ODBC to our AutoTag program and that is the key part. -- Show quotethanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Kevin Yu [MSFT]" wrote: > Hi Dave, > > 1. The schema information on Tables only have 3 restrictions. They are > database, owner, and table name. So we cannot filter the user tables with > GetSchema method. In this case, the simplest way to to create a DataView > object on the schemaTable. Here is an example: > > DataView dv = new DataView(schemaTable); > dv.RowFilter = "TABLE_TYPE='TABLE'"; > > Then dv will show user tables only. > > 2. The GetSchema("procedures") on SQL Server is actually calling > sp_stored_procedures to get all the procedures back. The returned table > from sp_stored_procedures didn't include an identifier to show if this > stored procedure is a system procedure or user one. In this case, we are > unable to know whether it is a system procedure. > > It seems to be a limitation. Sorry for the inconvenience. > > Kevin Yu > Microsoft Online Community Support > ================================================== > > (This posting is provided "AS IS", with no warranties, and confers no > rights.) > > That's what I was afraid of.
What is weird is the returned schemas all have the same type in their row data. You would think that the type would show system vs user. But - with this info we can add ODBC to our AutoTag program and that is the key part. -- Show quotethanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm "Kevin Yu [MSFT]" wrote: > Hi Dave, > > 1. The schema information on Tables only have 3 restrictions. They are > database, owner, and table name. So we cannot filter the user tables with > GetSchema method. In this case, the simplest way to to create a DataView > object on the schemaTable. Here is an example: > > DataView dv = new DataView(schemaTable); > dv.RowFilter = "TABLE_TYPE='TABLE'"; > > Then dv will show user tables only. > > 2. The GetSchema("procedures") on SQL Server is actually calling > sp_stored_procedures to get all the procedures back. The returned table > from sp_stored_procedures didn't include an identifier to show if this > stored procedure is a system procedure or user one. In this case, we are > unable to know whether it is a system procedure. > > It seems to be a limitation. Sorry for the inconvenience. > > Kevin Yu > Microsoft Online Community Support > ================================================== > > (This posting is provided "AS IS", with no warranties, and confers no > rights.) > >  |
|||||||||||||||||||||||
Other interesting topics