Syscoulmns Details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syscoulmns Details

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-18 : 08:32:07

Dear all Here ihave the query,
SELECT OBJECT_NAME(b.id) FROM apaudit..syscolumns a,apaudit..sysobjects b
WHERE a.name like 'amend_no%' and a.id = b.id and b.type = 'u'

But it retrives the master database syscolumns details

what is the wrong with my query

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-18 : 08:46:24

The OBJECT_NAME() function always works against the current database, and in the query you've written you don't even need it. Try this:

SELECT a.name FROM apaudit..syscolumns a
inner join apaudit..sysobjects b on a.id = b.id
WHERE a.name like 'amend_no%' and b.type = 'u'

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-18 : 09:21:55

Hi,robvolk It's Working fine Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 09:30:34

Other method is

SELECT C.column_name FROM Information_schema.columns C
inner join Information_schema.Tables T on C.table_name=T.table_name
where T.table_type='BASE TABLE' and C.Column_name like 'amend_no%'

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-19 : 00:30:44

Hi Mathi Very Thanks. I already recived so many suggesions from you through jothi kannan

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 01:35:32