Is it possible to concatenate query results to form a new query?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

I have now:

SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Could I include the results of the first query to the tags in second one in one query and how? I would like to avoid calling the every schema, table and column by hand to check only minor things in every table.

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

edited 1 hour ago

asked 1 hour ago

mico

2861516

SET @sql='SELECT ' + <COLUMN_NAME> + ' FROM ' + <TABLE_SCHEMA> + '.' + <TABLE_NAME>; EXEC (@sql);. Iterate by recordset (or concate in a query, then execute).
â€“Â Akina
1 hour ago

@Akina Could you elaborate that as an answer, seems meaningful approach?
â€“Â mico
1 hour ago

add a commentÂ |Â

up vote
1
down vote

favorite

I have now:

SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

edited 1 hour ago

asked 1 hour ago

mico

2861516

SET @sql='SELECT ' + <COLUMN_NAME> + ' FROM ' + <TABLE_SCHEMA> + '.' + <TABLE_NAME>; EXEC (@sql);. Iterate by recordset (or concate in a query, then execute).
â€“Â Akina
1 hour ago

@Akina Could you elaborate that as an answer, seems meaningful approach?
â€“Â mico
1 hour ago

add a commentÂ |Â

up vote
1
down vote

favorite

I have now:

SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

edited 1 hour ago

asked 1 hour ago

mico

2861516

I have now:

SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

sql-server sql-server-2012 information-schema

edited 1 hour ago

asked 1 hour ago

mico

2861516

edited 1 hour ago

asked 1 hour ago

mico

2861516

edited 1 hour ago

asked 1 hour ago

mico

2861516

asked 1 hour ago

mico

2861516

asked 1 hour ago

mico

2861516

SET @sql='SELECT ' + <COLUMN_NAME> + ' FROM ' + <TABLE_SCHEMA> + '.' + <TABLE_NAME>; EXEC (@sql);. Iterate by recordset (or concate in a query, then execute).
â€“Â Akina
1 hour ago

@Akina Could you elaborate that as an answer, seems meaningful approach?
â€“Â mico
1 hour ago

add a commentÂ |Â

SET @sql='SELECT ' + <COLUMN_NAME> + ' FROM ' + <TABLE_SCHEMA> + '.' + <TABLE_NAME>; EXEC (@sql);. Iterate by recordset (or concate in a query, then execute).
â€“Â Akina
1 hour ago

@Akina Could you elaborate that as an answer, seems meaningful approach?
â€“Â mico
1 hour ago

SET @sql='SELECT ' + <COLUMN_NAME> + ' FROM ' + <TABLE_SCHEMA> + '.' + <TABLE_NAME>; EXEC (@sql);. Iterate by recordset (or concate in a query, then execute).
â€“Â Akina
1 hour ago

@Akina Could you elaborate that as an answer, seems meaningful approach?
â€“Â mico
1 hour ago

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

Unless you are explicitly trying to write queries that work in multiple engines, I recommend sticking to the catalog views in the sys schema rather than INFORMATION_SCHEMA (see this and this). You'll need dynamic SQL for this anyway, and in other engines the syntax for this will vary greatly:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

SET @sql = STUFF(@sql, 1, 10, N'');
...

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

Thanks for quick reply. My @sql declaration ended up in form: SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219627%2fis-it-possible-to-concatenate-query-results-to-form-a-new-query%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

SET @sql = STUFF(@sql, 1, 10, N'');
...

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

Thanks for quick reply. My @sql declaration ended up in form: SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

add a commentÂ |Â

up vote
3
down vote

accepted

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

SET @sql = STUFF(@sql, 1, 10, N'');
...

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

Thanks for quick reply. My @sql declaration ended up in form: SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

add a commentÂ |Â

up vote
3
down vote

accepted

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

SET @sql = STUFF(@sql, 1, 10, N'');
...

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';

EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

SET @sql = STUFF(@sql, 1, 10, N'');
...

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

edited 12 mins ago

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

answered 1 hour ago

Aaron Bertrandâ™¦

146k19280468

Thanks for quick reply. My @sql declaration ended up in form: SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

add a commentÂ |Â

Thanks for quick reply. My @sql declaration ended up in form: SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

Thanks for quick reply. My @sql declaration ended up in form:

SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'

to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

Thanks for quick reply. My @sql declaration ended up in form:

SELECT @sql += N'SELECT distinct' + QUOTENAME(c.name) + N' as ' + QUOTENAME(t.name) + N' FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'

to be able to name data I found. Without that change, the results were with same title and thus useless for my purposes.
â€“Â mico
30 mins ago

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky