Extracting the user tables with a given column name.

Database

Extracting the user tables with a given column name.

Hi

select * from DBC.Columns where ColumnName = 'ACC_NO'

The above query results with the System tables also ....
but i need only the user tables .......

Can u pls suggest me the way to do it.

Regards
1 REPLY

Re: Extracting the user tables with a given column name.

Hi Dileep !

You have to join to DBC.DataBases to get the information wether it is a System or User-DB.

SELECT *
FROM DBC.Columns AS C
INNER JOIN DBC.DataBases AS D ON (C.DataBaseName = D.DataBaseName)
WHERE
(
(D.DataBaseName NOT IN ('DBC', 'CrashDumps')) AND
-- UserDataBase
(D.DBKind = 'U') AND
(C.ColumnName LIKE '%test%')
);

Regards

Robert