InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:38:56 GMT20http://www.sqlservercentral.com/Forums/Topic1001343-1043-1.aspx[quote][b]Lynn Pettis (10/8/2010)[/b][hr]We all know that in SQL Server that there is a system table that tells you about [b]all the columns in each table [/b]in a database, sys.columns (in SQL Server 2005/2008).Is there an equivalent system table or view that provides the same information? I'm not having much luck finding one, but perhaps one of you out there that also are familiar with Oracle may be able to assist.[/quote]check dba_tab_columns, you have to have dba privileges to have access to it.Mon, 11 Oct 2010 08:57:57 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1001343-1043-1.aspxI also founf ALL_TAB_COLUMNS that provided me with the info I needed.I will definately take a closer look and the code you provided when I have a little more time. Got some project stuff I need to work on right now.Thanks Lowell.Fri, 08 Oct 2010 11:16:23 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1001343-1043-1.aspxalso this will really get you what you are after:[code]select * from all_views where LEFT(view_name,5) = 'USER_'[/code]Fri, 08 Oct 2010 09:04:44 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1001343-1043-1.aspxthere's a few suites of views of the objects: ALL_TABLES, DBA_TABLES and USER_TABLES;i try to stick with USER_tables, which are filtered to the schema you belong to (well the oracle user)USER_TABLES is ~sys.tablesuser_tab_columns is ~ sys.columnshere's an example i use when getting tables in a FK hierarchy order:[code]WITH MyCommonTableExpressionAS ( SELECT a.table_name as child_table, b.table_name parent_table, colb.column_name as child_column from user_constraints a INNER JOIN user_constraints b ON a.r_constraint_name = b.constraint_name INNER JOIN user_cons_columns conb ON conb.constraint_name = b.constraint_name INNER JOIN user_tab_columns colb ON colb.table_name = conb.table_name AND colb.column_name = conb.column_name WHERE a.constraint_type = 'R' ) SELECT parent_table, child_table, child_column, 1 as lvl FROM MyCommonTableExpression WHERE parent_table='GMACT' START WITH parent_table = 'GMACT' CONNECT BY PRIOR child_table = parent_table[/code]Fri, 08 Oct 2010 08:54:57 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1001343-1043-1.aspxWe all know that in SQL Server that there is a system table that tells you about all the columns in each table in a database, sys.columns (in SQL Server 2005/2008).Is there an equivalent system table or view that provides the same information? I'm not having much luck finding one, but perhaps one of you out there that also are familiar with Oracle may be able to assist.Fri, 08 Oct 2010 08:26:49 GMTLynn Pettis