April 4, 2012 at 10:12 pm
I want to pull the same information from 100 or so odd tables. Could someone suggest a better way than 100 Select statements that are unioned for example. I have a list of the tables in a query which I would prefer to gather the required tables from.
For example:
Select X
From Table A
Union Select X
From Table B
and so forth...
Thank you for your help.
April 4, 2012 at 11:21 pm
EXEC sp_MSForEachTable 'SELECT X FROM ?'
This will only work if the column X is in all the tables of the current database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 5, 2012 at 12:46 am
If you wanted to do this regularly then a view might be an answer.
Below is a script to generate the view using a SP and a cursor. Not that I am advocating using cursor for other stuff.
create database TestTheory;
go
use TestTheory;
go
create table Test1(
Col1 int,
Col2 varchar(30),
)
create table Test2(
Col1 int,
Col2 varchar(30),
Col3 varchar(40)
)
go
insert into Test1 values (100,'Fred')
insert into Test2 values (100,'Fred','Flintstone')
insert into Test2 values (101,'Barney','Rubble')
go
alter proc Generate_Union @columnsToInclude varchar(255), @unionType varchar(20)
as
begin
if exists (select * from sys.objects where Name = 'Generated_Union_View')
drop view Generated_Union_View
declare @thisTableName varchar(255)
declare @addTable varchar(400)
declare @ViewDefinition varchar(8000)=''
declare @addPart varchar(20) = ''
declare tableCursor cursor for select '[' + SC.name+'].['+SO.name+']' as FullName
from sys.objects as SO
inner join sys.schemas as SC
on SO.schema_id = SC.schema_id
where type ='U'
open tableCursor
fetch next from tableCursor into @thisTableName
while @@FETCH_STATUS = 0
begin
set @addTable = 'SELECT '+
@columnsToInclude + ' from ' + @thisTableName
set @ViewDefinition = @viewDefinition + @addPart + @addTable
set @addPart = ' ' + @unionType + ' '
fetch next from tableCursor into @thisTableName
end
set @ViewDefinition = 'CREATE VIEW GENERATED_UNION_VIEW AS ' + @ViewDefinition
exec (@ViewDefinition)
close tableCursor
deallocate tableCursor
end
go
exec dbo.Generate_Union 'col1,col2','UNION'
select * from Generated_Union_View
go
exec dbo.Generate_Union 'col1,col2','UNION ALL'
select * from Generated_Union_View
go
Fitz
April 5, 2012 at 1:56 am
Here's one possible way:
-- =========================================== --
-- SETUP --
-- =========================================== --
-- CLEANUP
IF OBJECT_ID('tempdb..#table1') IS NOT NULL
BEGIN
DROP TABLE #table1;
DROP TABLE #table2;
DROP TABLE #table3;
DROP TABLE #table4;
DROP TABLE #table5;
DROP TABLE #tableNames;
END
-- CREATE TEST TABLES
CREATE TABLE #table1 (Name nvarchar(50));
CREATE TABLE #table2 (Name nvarchar(50));
CREATE TABLE #table3 (Name nvarchar(50));
CREATE TABLE #table4 (Name nvarchar(50));
CREATE TABLE #table5 (Name nvarchar(50));
-- INSERT SOME VALUES
INSERT INTO #table1 VALUES ('Joe');
INSERT INTO #table2 VALUES ('Mary');
INSERT INTO #table3 VALUES ('Harry');
INSERT INTO #table4 VALUES ('John');
INSERT INTO #table5 VALUES ('Susan');
-- CREATE A TABLE TO STORE TABLE NAMES
CREATE TABLE #tableNames (Name sysname);
-- POPULATE TABLE NAMES
INSERT INTO #tableNames VALUES('#table1');
INSERT INTO #tableNames VALUES('#table2');
INSERT INTO #tableNames VALUES('#table3');
INSERT INTO #tableNames VALUES('#table4');
INSERT INTO #tableNames VALUES('#table5');
-- =========================================== --
-- SOLUTION --
-- =========================================== --
-- DECLARE A VARIABLE FOR DYNAMIC SQL
DECLARE @sql nvarchar(max);
-- CREATE THE SQL STATEMENT DYNAMICALLY FROM THE TABLE NAMES
SET @sql = STUFF((
SELECT 'UNION ALL' + char(10) +
'SELECT Name FROM ' + Name + ' ' + char(10) AS [text()]
FROM #tableNames
FOR XML PATH('')
),1,LEN('UNION ALL'),SPACE(0))
-- EXECUTE THE STATEMENT
EXEC(@sql);
-- Gianluca Sartori
April 5, 2012 at 2:07 am
@Gianluca: I wish everyone posted such nicely formatted code to the forum 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 5, 2012 at 2:13 am
Koen Verbeeck (4/5/2012)
@Gianluca: I wish everyone posted such nicely formatted code to the forum 🙂
Thanks! I learned this from Jeff. 🙂
-- Gianluca Sartori
April 5, 2012 at 2:15 am
Gianluca Sartori (4/5/2012)
Koen Verbeeck (4/5/2012)
@Gianluca: I wish everyone posted such nicely formatted code to the forum 🙂Thanks! I learned this from Jeff. 🙂
I should have said "this and much more"!
But it goes without saying.
-- Gianluca Sartori
April 5, 2012 at 9:26 pm
Gianluca,
Thank you so much for your assistance. How Could incorporate the the source table as a column/field? Simply 'Table' as 'Source Table'.
April 6, 2012 at 2:39 am
chris86 (4/5/2012)
Gianluca,Thank you so much for your assistance. How Could incorporate the the source table as a column/field? Simply 'Table' as 'Source Table'.
I don't think I understand your question. Can you clarify please? (Maybe with an example?)
-- Gianluca Sartori
April 6, 2012 at 5:33 am
declare @colname nvarchar(max),@coltype nvarchar(10)
set @colname='purchase_order_no'
set @coltype='int'
select O.name,ROW_NUMBER() Over (Order by o.name)RNo ,c.user_type_id,SCHEMA_NAME(schema_id)SCHEMA_NAMEs into #temp from sys.columns C inner join sys.objects O on C.object_id=O.object_id where C.name like @colname and O.type='U' and C.user_type_id=TYPE_ID(@coltype)
select * from #temp
Declare @RowCount int,@name nvarchar(max),@sql nvarchar(max),@schema_name nvarchar(max)
set @RowCount=1
set @sql=''
if ((Select COUNT(*) from #temp)>0)
Begin
While(@RowCount <>(Select COUNT(*) from #temp))
Begin
select @name=name ,@schema_name=SCHEMA_NAMEs from #temp where Rno=@RowCount
if(LEN(@sql)=0)
SET @sql += 'SELECT ' + @colname +' FROM ' + @schema_name +'.' + @name + char(10)
else
SET @sql += ' Union SELECT ' + @colname +' FROM ' + @schema_name +'.' + @name + char(10)
Set @RowCount+=1
End
Drop table #temp
Print @SQl
exec @sql
End
Give Column name and Datatype of that column as input
Regards
Guru
April 6, 2012 at 6:37 am
-- DECLARE A VARIABLE FOR DYNAMIC SQL
DECLARE @sql nvarchar(max);
DECLARE @colname NVARCHAR(max)
,@coltype NVARCHAR(10)
SET @colname = 'purchase_order_no'
SET @coltype = 'int'
-- CREATE THE SQL STATEMENT DYNAMICALLY FROM THE TABLE NAMES
SET @sql = STUFF((
SELECT 'UNION ALL' + char(10) +
'SELECT ' + QUOTENAME(C.name) + char(10) +
'FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id)) + '.' + QUOTENAME(O.Name) + ' ' + char(10) AS [text()]
FROM sys.columns C
INNER JOIN sys.objects O
ON C.object_id = O.object_id
WHERE C.NAME LIKE @colname
AND O.type = 'U'
AND C.user_type_id = TYPE_ID(@coltype)
FOR XML PATH('')
),1,LEN('UNION ALL'),SPACE(0))
-- EXECUTE THE STATEMENT
EXEC(@sql);
Edit: fixed indenting
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply