Display table name and 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

SQL Server 2000 Forums

Transact-SQL (2000)

Display table name and column name

Author

Topic

jaypee_s
Starting Member

22 Posts

Posted - 2007-09-04 : 03:16:36

From this view
select * from INFORMATION_SCHEMA.COLUMNS

is it possible to display rows like

tablename columname
a b
c
d
e f
g
h

Thanks in advance
jp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:23:41

You have given us very little information to work on.

select Table_Name, case when Column_Name in ('b', 'f') then column_name else '' end
from INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name IN ('a', 'c', 'd', 'e', 'g', 'h')
order by table_name

E 12°55'05.25"
N 56°04'39.16"

jaypee_s
Starting Member

22 Posts

Posted - 2007-09-04 : 04:39:02

sorry for the Inconvenience...
select * from INFORMATION_SCHEMA.COLUMNS

is it possible to display rows like

tablename columname
a----------- b
------------ c
------------ d
e ------------f
------------ g
------------ h

A and E are the table names, b,c,d are the column name belongs to 'A' and f,g,h are the colum name belongs to 'E'.

thanks
jp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 04:56:21

This is a presentation issue, not a database issue.
Can't you put a check mark on "suppress duplicates" in crystal reports?

SELECT		CASE (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS AS c2 WHERE c2.TABLE_NAME = c1.TABLE_NAME AND c2.COLUMN_NAME < c1.COLUMN_NAME)
			WHEN 0 THEN c1.TABLE_NAME
			ELSE ''
		END AS TABLE_NAME,
		c1.COLUMN_NAME
FROM		INFORMATION_SCHEMA.COLUMNS AS c1
ORDER BY	c1.TABLE_NAME,
		c1.COLUMN_NAME

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-04 : 05:38:21

quote:
Originally posted by jaypee_s

sorry for the Inconvenience...
select * from INFORMATION_SCHEMA.COLUMNS

is it possible to display rows like

tablename columname
a----------- b
------------ c
------------ d
e ------------f
------------ g
------------ h

A and E are the table names, b,c,d are the column name belongs to 'A' and f,g,h are the colum name belongs to 'E'.

thanks
jp

Where do you want to show data?
This is the classic example of Suppress if duplicated which should be done in the front end application if use want to show data there

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 05:39:57

by 42 minutes...

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-04 : 05:43:05

It is a presentation problem

select Case when ordinal_position = 1 then table_name else '' end as [Table Name],
column_name as [Column Name]
from INFORMATION_SCHEMA.COLUMNS
order by table_name, ordinal_position

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 05:47:10

quote:
Originally posted by pbguy

select Case when ordinal_position = 1 then table_name else '' end as [Table Name],
column_name as [Column Name]
from INFORMATION_SCHEMA.COLUMNS
order by table_name, ordinal_position

it works, if the columns are to be sorted by column order, not column name.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-04 : 05:55:22

quote:
Originally posted by Peso

by 42 minutes...

E 12°55'05.25"
N 56°04'39.16"

Yes it is. I thought you didnt specify that when I saw the code

Madhivanan

Failing to plan is Planning to fail

jaypee_s
Starting Member

22 Posts

Posted - 2007-09-04 : 06:38:55

Thank you all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-04 : 07:07:45

quote:
Originally posted by jaypee_s

Thank you all

But you didnt answer to my question

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 07:12:17

There is a new breed of Yaks in town, who only wants to get the answer they ask for and not trying to learn.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 06:54:03

quote:
Originally posted by Peso

There is a new breed of Yaks in town, who only wants to get the answer they ask for and not trying to learn.

E 12°55'05.25"
N 56°04'39.16"

Yes it is. May be they dont want to give more informations

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources