get value from columns using list of 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 value from columns using list of tables

Author

Topic

ecivgamer
Starting Member

7 Posts

Posted - 2014-02-05 : 12:36:33

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 - 2014-02-05 : 12:47:08

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?

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-06 : 02:19:15

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

ecivgamer
Starting Member

7 Posts

Posted - 2014-02-06 : 04:19:27

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?

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-06 : 06:02:24

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

ecivgamer
Starting Member

7 Posts

Posted - 2014-02-06 : 06:47:50

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!

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-07 : 04:14:11

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

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-07 : 04:15:02

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources