Advanced System mapping and Sys views

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 2005 Forums

Transact-SQL (2005)

Advanced System mapping and Sys views

Author

Topic

lior3790
Starting Member

46 Posts

Posted - 2007-03-21 : 04:49:52

Hello to all,

i'm trying to view the system information with additional the proper column text for view and designing (or fixing) tha catalog.

I'm using this query:

select distinct c.name columnName,c.max_length,t.name 
,tab.name tableName,(select distinct c.name from tab.name,sys.columns where  sys.columns.name=c.name)as test 
 from sys.tables as tab ,sys.columns as c,sys.types as t,sys.databases as db
where c.system_type_id=t.system_type_id  and c.object_id=tab.object_id and t.name!='sysname'

 order by 
tab.name

and it works fine, but i'm trying also to add the table column actual text (text or numbers i have in the cells).

how can i add that column?

lior3790
Starting Member

46 Posts

Posted - 2007-03-21 : 04:51:21

Sorry,

this is the working query i'm looking sulotion for:




select distinct c.name columnName,c.max_length,t.name 
,tab.name tableName 
 from sys.tables as tab ,sys.columns as c,sys.types as t,sys.databases as db
where c.system_type_id=t.system_type_id  and c.object_id=tab.object_id and t.name!='sysname'

 order by 
tab.name

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 04:54:20

Do you mean to say you want to add actual record contents for that column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

lior3790
Starting Member

46 Posts

Posted - 2007-03-21 : 04:57:17

Exactly

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 04:59:33

Why?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 04:59:40

I don't think that is possible because resultset has to be square

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 05:01:39

You can solve this with dynamic SQL, but again, why?
Potentially you can come up with million of records...

Peter Larsson
Helsingborg, Sweden

lior3790
Starting Member

46 Posts

Posted - 2007-03-21 : 05:04:52

I've started to work with this company on a built project.

there is a lot of mess with the Catalog and i need to redesign all the tables and consider the actual cell text.

for example: now if you want to look for someone's age in a table, you will have to look for a String, funny as it sounds.

and its just the begining.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 05:06:30

I think this is a good effort, but the method is scary.

Peter Larsson
Helsingborg, Sweden

lior3790
Starting Member

46 Posts

Posted - 2007-03-21 : 05:14:51

I'm pretty brave.

can you redirect me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 06:01:05

No.
That would take to much time for me, learning you dynamic sql.

Peter Larsson
Helsingborg, Sweden