Avoid printout of joined column name

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

General SQL Server Forums

New to SQL Server Programming

Avoid printout of joined column name

Author

Topic

ben_rendel
Starting Member

2 Posts

Posted - 2010-10-27 : 07:49:42

Hi

This code reports all columns of all tables which have a column named
'ContactServiceID'

e.g.

ContactEmail1Table ContactServiceID
ContactEmail1Table EmailAddress
ContactEmail1Table EmailDisplayAs
ContactEmail2Table ContactServiceID
ContactEmail2Table EmailAddress
ContactEmail2Table EmailDisplayAs

how can i exclude 'ContactServiceID' reporting ?
(set filter seems to have no influence)

code: (ran via MS Studio, SQL 2005)
=====

use MSSmallBusiness

SELECT table_name=sysobjects.name,
column_name=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.name IN (
SELECT DISTINCT table_name=sysobjects.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE syscolumns.name = 'ContactServiceID'
)and (sysobjects.xtype = 'U' or sysobjects.xtype = 'S')

edit: moved to proper forum

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 07:56:35

[code]
SELECT * FROM
(
SELECT table_name=sysobjects.name,
column_name=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE sysobjects.name IN (
SELECT DISTINCT table_name=sysobjects.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
WHERE syscolumns.name = 'ContactServiceID'
)and (sysobjects.xtype = 'U' or sysobjects.xtype = 'S')
)dt
WHERE column_name <> 'ContactServiceID'
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.