SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get value from columns using list of tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ecivgamer
Starting Member

7 Posts

Posted - 02/05/2014 :  12:36:33  Show Profile  Reply with Quote
Hi all,

how do I get value from column using list of tables?

For example, I have list:

schema_name, table_name, column_name

How do I get:

schema_name, table_name, column_name, column_value

?

ecivgamer
Starting Member

7 Posts

Posted - 02/05/2014 :  12:47:08  Show Profile  Reply with Quote
Upd: What I mean...

Let's say I have scheme TRAIN with table FLOWERS and column COLOUR.

Table ANIMALS has two records, so possible values or column COLOUR are GREY and GREEN.

So the result of my query should be:


schema_name table_name column_name column_value

TRAIN FLOWERS COLOUR GREY

TRAIN FLOWERS COLOUR GREEN

How do I perform it?
Go to Top of Page

Avinaash.S786
Starting Member

9 Posts

Posted - 02/06/2014 :  02:19:15  Show Profile  Reply with Quote
Try This
DECLARE @strQry1 varchar(max)
SET @strQry1 =(select STUFF((SELECT TOP 100 PERCENT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value from '+s.name +'.'+ o.name as qry
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)
PRINT (@strQry1)
EXEC (@strQry1)


Avinaash S

Edited by - Avinaash.S786 on 02/06/2014 02:33:05
Go to Top of Page

ecivgamer
Starting Member

7 Posts

Posted - 02/06/2014 :  04:19:27  Show Profile  Reply with Quote
quote:
Originally posted by Avinaash.S786

Try This
DECLARE @strQry1 varchar(max)
SET @strQry1 =(select STUFF((SELECT TOP 100 PERCENT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value from '+s.name +'.'+ o.name as qry
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)
PRINT (@strQry1)
EXEC (@strQry1)


Avinaash S




Works great on test server, but got error at production server:

quote:

Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xfallback_dbid' as column_name, Convert(varchar,xfallback_dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'version' as column_name, Convert(varchar,version) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_low' as column_name, Convert(varchar,xfallback_low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_drive' as column_name, Convert(varchar,xfallback_drive) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'low' as column_name, Convert(varchar,low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'high' as column_name, Convert(varchar,high) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'phyname' as column_name, Convert(varchar,phyname) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xfallback_vstart' as column_name, Convert(varchar,xfallback_vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'segmap' as column_name, Convert(varchar,segmap) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'lstart' as column_name, Convert(varchar,lstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'sizepg' as column_name, Convert(varchar,sizepg) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'vstart' as column_name, Convert(varchar,vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Id' as column_name, Convert(varchar,Id) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Name' as column_name, Convert(varchar,Name) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Status' as column_name, Convert(varchar,Status) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'GroupId' as column_name, Convert(varchar,GroupId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'OrderId' as column_name, Convert(varchar,OrderId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'CreatorId' as column_name, Convert(varchar,CreatorId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Created' as column_name, Convert(varchar,Created) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'ChangerId' as column_name, Convert(varchar,ChangerId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Changed' as column_name, Convert(varchar,Changed) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'b2_passporttypeid' as column_name, Convert(varchar,b2_passporttypeid) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'EVENT_ID' as column_name, Convert(varchar,EVENT_ID) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_NAME' as column_name, Convert(varchar,RULE_NAME) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_INFORMATION' as column_name, Convert(varchar,RULE_INFORMATION) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'lastrun' as column_name, Convert(varchar,lastrun) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'cpu_busy' as column_name, Convert(varchar,cpu_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'io_busy' as column_name, Convert(varchar,io_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'idle' as column_name, Convert(varchar,idle) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_received' as column_name, Convert(varchar,pack_received) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_sent' as column_name, Convert(varchar,pack_sent) as column_value from dbo.spt_monitor Union All Select 'dbo' a
Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.



How do I resolve it?
Go to Top of Page

Avinaash.S786
Starting Member

9 Posts

Posted - 02/06/2014 :  06:02:24  Show Profile  Reply with Quote
I have printed the query as you can see. The query is exceeding the limit of varchar(max). That is number of columns and tables in production server are much greater than that in test server. So the query in @strQry1 is incomplete. So try splitting the query into two variables as in the below query.


DECLARE @strQry1 varchar(max)
DECLARE @strQry2 varchar(max)
DECLARE @rowCount bigint
SET @rowCount = (
SELECT COUNT('X')
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
)
DECLARE @tempTable TABLE (
ID BIGINT IDENTITY(1,1),
strQry varchar(max))
INSERT INTO @tempTable (strQry)
SELECT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value from '+s.name +'.'+ o.name as qry
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
--SELECT * FROM @tempTable
SET @strQry1 =(select STUFF((SELECT strQry
FROM
@tempTable
where ID < (SELECT @rowCount/2 )


FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)

SET @strQry2 =(select STUFF((SELECT strQry
FROM
@tempTable
where ID >= (SELECT @rowCount/2 )


FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)

PRINT (@strQry1 + ' UNION ALL ' + @strQry2)
EXEC (@strQry2 + ' UNION ALL ' + @strQry2)





Avinaash S

Edited by - Avinaash.S786 on 02/06/2014 06:02:52
Go to Top of Page

ecivgamer
Starting Member

7 Posts

Posted - 02/06/2014 :  06:47:50  Show Profile  Reply with Quote
Avinaash S,

that's great!

Now how do I select only those record that have '%acc%' in column_value column?

... where column_value like '%acc%'

Thanks ahead!
Go to Top of Page

Avinaash.S786
Starting Member

9 Posts

Posted - 02/07/2014 :  04:14:11  Show Profile  Reply with Quote
Insert the result into a table variable. And then do the operation on it. Like for example

Change

EXEC (@strQry2 + ' UNION ALL ' + @strQry2)

As

DECLARE @tempTable2 TABLE (
[schema_name] varchar(max),
table_name varchar(max),
column_name varchar(max),
column_value varchar(max)
)
INSERT INTO @tempTable2
EXEC (@strQry2 + ' UNION ALL ' + @strQry2)
SELECT * FROM @tempTable2 where column_value LIKE '%acc%'

Avinaash S
Go to Top of Page

Avinaash.S786
Starting Member

9 Posts

Posted - 02/07/2014 :  04:15:02  Show Profile  Reply with Quote
That is query will be something like this


DECLARE @strQry1 varchar(max)
DECLARE @strQry2 varchar(max)
DECLARE @rowCount bigint
SET @rowCount = (
SELECT COUNT('X')
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
)
DECLARE @tempTable TABLE (
ID BIGINT IDENTITY(1,1),
strQry varchar(max))
INSERT INTO @tempTable (strQry)
SELECT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value from '+s.name +'.'+ o.name as qry
FROM
sys.all_columns c
LEFT JOIN
sys.all_objects o
on
c.object_id=o.object_id
left join
sys.schemas s
on s.schema_id=o.schema_id
where o.type='u'
--SELECT * FROM @tempTable
SET @strQry1 =(select STUFF((SELECT strQry
FROM
@tempTable
where ID < (SELECT @rowCount/2 )


FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)

SET @strQry2 =(select STUFF((SELECT strQry
FROM
@tempTable
where ID >= (SELECT @rowCount/2 )


FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)

PRINT (@strQry1 + ' UNION ALL ' + @strQry2)
DECLARE @tempTable2 TABLE (
[schema_name] varchar(max),
table_name varchar(max),
column_name varchar(max),
column_value varchar(max)
)
INSERT INTO @tempTable2
EXEC (@strQry2 + ' UNION ALL ' + @strQry2)
SELECT * FROM @tempTable2 where column_value LIKE '%acc%'




Avinaash S
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000