DECLARE @SQL NVARCHAR(MAX) DECLARE @MAX INT DECLARE @TABLE TABLE(NA NVARCHAR(128),CO INT) INSERT INTO @TABLE SELECT T1.NAME,COUNT(T2.id)CO FROM SYS.SYSOBJECTS T1 JOIN SYS.SYSCOLUMNS T2 ON T1.id=T2.id WHERE T1.[name]IN('fram.100001_tab','fram.100002_tab','fram.100003_tab') GROUP BY T1.[name] ;SELECT @SQL='SELECT * FROM ['+NA+'] WHERE 1=0 ',@MAX=CO FROM( SELECT TOP 1 NA,CO FROM @TABLE ORDER BY CO DESC )T SELECT @SQL=@SQL+'UNION ALL SELECT *'+REPLICATE(',NULL',@MAX-CO)+' FROM ['+NA+']' FROM @TABLE --PRINT @SQL EXEC(@SQL)你参考下,应该可以~~
--用到的表 ;WITH tables(table_name) AS ( SELECT 'fram.100001_tab' UNION ALL SELECT 'fram.100002_tab' UNION ALL SELECT 'fram.100003_tab' UNION ALL SELECT 'fram.100001_tab' ) SELECT * INTO #tables FROM tables--表各自的字段 SELECT t.table_name, c.name column_name, c.column_id INTO #columns FROM #tables t JOIN sys.columns c ON c.object_id = OBJECT_ID(t.table_name)--所有字段全集 SELECT column_name, ROW_NUMBER() OVER(ORDER BY MIN(column_id), MIN(table_name)) column_id INTO #outColumns FROM #columns GROUP BY column_name--各表用NULL补齐字段 SELECT t.table_name, ISNULL(c.column_name,'NULL AS '+oc.column_name) AS column_name, oc.column_id INTO #inColumns FROM #tables t JOIN #outColumns oc ON 1=1 LEFT JOIN #columns c ON t.table_name = c.table_name AND oc.column_name = c.column_name /* SELECT * FROM #tables SELECT * FROM #columns SELECT * FROM #outColumns SELECT * FROM #inColumns */DECLARE @sql nvarchar(max);WITH tableSQL AS ( SELECT 'SELECT ' + Stuff((SELECT ','+column_name FROM #inColumns ic WHERE ic.table_name = t.table_name for xml path('') ), 1,1,'') + ' FROM ' + t.table_name AS sql FROM #tables t ) SELECT @sql = Stuff((SELECT ' UNION ALL '+sql FROM tableSQL ic for xml path('') ), 1,11,'') PRINT @sql
DECLARE @MAX INT
DECLARE @TABLE TABLE(NA NVARCHAR(128),CO INT)
INSERT INTO @TABLE
SELECT T1.NAME,COUNT(T2.id)CO FROM SYS.SYSOBJECTS T1
JOIN SYS.SYSCOLUMNS T2 ON T1.id=T2.id
WHERE T1.[name]IN('fram.100001_tab','fram.100002_tab','fram.100003_tab')
GROUP BY T1.[name]
;SELECT @SQL='SELECT * FROM ['+NA+'] WHERE 1=0 ',@MAX=CO FROM(
SELECT TOP 1 NA,CO FROM @TABLE
ORDER BY CO DESC
)T
SELECT @SQL=@SQL+'UNION ALL SELECT *'+REPLICATE(',NULL',@MAX-CO)+' FROM ['+NA+']' FROM @TABLE
--PRINT @SQL
EXEC(@SQL)你参考下,应该可以~~
;WITH tables(table_name) AS (
SELECT 'fram.100001_tab' UNION ALL
SELECT 'fram.100002_tab' UNION ALL
SELECT 'fram.100003_tab' UNION ALL
SELECT 'fram.100001_tab'
)
SELECT *
INTO #tables
FROM tables--表各自的字段
SELECT t.table_name,
c.name column_name,
c.column_id
INTO #columns
FROM #tables t
JOIN sys.columns c
ON c.object_id = OBJECT_ID(t.table_name)--所有字段全集
SELECT column_name,
ROW_NUMBER() OVER(ORDER BY MIN(column_id), MIN(table_name)) column_id
INTO #outColumns
FROM #columns
GROUP BY column_name--各表用NULL补齐字段
SELECT t.table_name,
ISNULL(c.column_name,'NULL AS '+oc.column_name) AS column_name,
oc.column_id
INTO #inColumns
FROM #tables t
JOIN #outColumns oc
ON 1=1
LEFT JOIN #columns c
ON t.table_name = c.table_name
AND oc.column_name = c.column_name
/*
SELECT * FROM #tables
SELECT * FROM #columns
SELECT * FROM #outColumns
SELECT * FROM #inColumns
*/DECLARE @sql nvarchar(max);WITH tableSQL AS (
SELECT 'SELECT ' +
Stuff((SELECT ','+column_name
FROM #inColumns ic
WHERE ic.table_name = t.table_name
for xml path('')
),
1,1,'') +
' FROM ' + t.table_name AS sql
FROM #tables t
)
SELECT @sql = Stuff((SELECT ' UNION ALL '+sql
FROM tableSQL ic
for xml path('')
),
1,11,'')
PRINT @sql