declare @esquema as varchar(50); set @esquema = 'dbo';
; with todo as
(
select '[' + sc.name + '].[' + tx.name + ']' [schema.table], tx.name [table], tx.id, sc.schema_id,
LTRIM(
ISNULL(
STUFF(
(
SELECT '";"' + c.name
FROM syscolumns c
LEFT JOIN sysobjects t on (t.id = c.id)
WHERE (t.id = tx.id)
AND t.uid in (select schema_id from sys.schemas where name = @esquema AND t.xtype = 'U')
ORDER BY c.name
FOR XML PATH ('')
), 1, 2, ' '
),
'Sin cols') + '"'
) AS [ColumnNames],
LTRIM(
ISNULL(
STUFF(
(
SELECT '], [' + c.name
FROM syscolumns c
LEFT JOIN sysobjects t on (t.id = c.id)
WHERE (t.id = tx.id)
AND t.uid in (select schema_id from sys.schemas where name = @esquema AND t.xtype = 'U')
ORDER BY c.name
FOR XML PATH ('')
), 1, 2, ' '
),
'Sin cols') + ']'
) AS [ColumnNamesQuery]
from sysobjects tx
left join sys.schemas sc on (tx.uid = sc.schema_id)
where tx.xtype = 'U'
and sc.name = @esquema
)
select a.[schema.table], a.[table], a.[id], a.[schema_id], a.[ColumnNames],
'SELECT ' + replace(replace(a.[ColumnNamesQuery], '";"', ', '), '"', '') + ' FROM ' + a.[schema.table] + ';' Comprobacion
from todo a
order by a.[table]
fjtello
commented
4 years ago
declare @esquema as varchar(50); set @esquema = 'dbo'; ; with todo as ( select '[' + sc.name + '].[' + tx.name + ']' [schema.table], tx.name [table], tx.id, sc.schema_id, LTRIM( ISNULL( STUFF( ( SELECT '";"' + c.name FROM syscolumns c LEFT JOIN sysobjects t on (t.id = c.id) WHERE (t.id = tx.id) AND t.uid in (select schema_id from sys.schemas where name = @esquema AND t.xtype = 'U') ORDER BY c.name FOR XML PATH ('') ), 1, 2, ' ' ), 'Sin cols') + '"' ) AS [ColumnNames], LTRIM( ISNULL( STUFF( ( SELECT '], [' + c.name FROM syscolumns c LEFT JOIN sysobjects t on (t.id = c.id) WHERE (t.id = tx.id) AND t.uid in (select schema_id from sys.schemas where name = @esquema AND t.xtype = 'U') ORDER BY c.name FOR XML PATH ('') ), 1, 2, ' ' ), 'Sin cols') + ']' ) AS [ColumnNamesQuery] from sysobjects tx left join sys.schemas sc on (tx.uid = sc.schema_id) where tx.xtype = 'U' and sc.name = @esquema ) select a.[schema.table], a.[table], a.[id], a.[schema_id], a.[ColumnNames], 'SELECT ' + replace(replace(a.[ColumnNamesQuery], '";"', ', '), '"', '') + ' FROM ' + a.[schema.table] + ';' Comprobacion from todo a order by a.[table]