get common columns name between tables

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

get common columns name between tables

Author

Topic

vfactor
Starting Member

11 Posts

Posted - 2006-08-08 : 16:04:03

Hi,

Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database

For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)

name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?

THANK YOU

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-08 : 16:17:01

You can use INFORMATION_SCHEMA.COLUMNS and do a self-join on COLUMN_NAME when you want to compare two tables.

CODO ERGO SUM

vfactor
Starting Member

11 Posts

Posted - 2006-08-08 : 16:50:59

Thank you Michael for your suggestion

I wrote a stored procedure to return common columns_name from a list of table (string delimited by ;) passing as parameter. Here it is if someone needs it

CREATE PROCEDURE sp_GET_COMMUN_COLUMNS
@lstTables varchar(4000)
AS

SET NOCOUNT ON

BEGIN
--This table will have all the table name
DECLARE @TableNames TABLE(Table_Name varchar(255))
--This table will contain all the columns name from all tables
DECLARE @TableColumns TABLE(Column_name varchar(255))

--Function iter_varcharlist_to_table will split the string into a table
INSERT INTO @TableNames SELECT nom FROM iter_varcharlist_to_table(@lstTables)
IF @@ERROR <> 0 GOTO PROBLEM

INSERT INTO @TableColumns SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN( SELECT Table_Name FROM @TableNames )
IF @@ERROR <> 0 GOTO PROBLEM

--Look for duplicate
SELECT Column_name, COUNT(Column_name) AS NumOccurrences
FROM @TableColumns
GROUP BY Column_name
HAVING (COUNT(Column_name) > 1)

IF @@ERROR <> 0 GOTO PROBLEM

END

RETURN 1

PROBLEM:
PRINT 'Erreur sp_GET_COMMUN_COLUMNS'

RETURN 0
GO

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-08 : 16:52:36

SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME IN (
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
GROUP BY COLUMN_NAME HAVING COUNT(*) > 1)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vfactor
Starting Member

11 Posts

Posted - 2006-08-08 : 16:58:51

There's an error in my sp

should DECLARE @count int

SELECT @count= COUNT(*) FROM @TableNames

so when look for duplicate

HAVING (COUNT(Column_name) = @count)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-08 : 17:00:03

You could use this in your procedure to find the columns that occur in all the tables you passed to it.


--Look for columms that are in all tables.
SELECT
	Column_name,
	COUNT(*) AS NumOccurrences
FROM
	@TableColumns
GROUP BY
	Column_name
HAVING
	COUNT(*) = (select Cnt = count(*) from @TableNames )

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-08 : 17:05:06

What exactly are you looking for?

Could you read the hint link in my sig and gives an idea of what the output is suppose to loook like?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources