jimbobmcgee (7/8/2013)

---

Not that I advocate this approach, at all, but the following should allow you to get a column list for any server/database/schema/table combo...

DECLARE @liststr NVARCHAR(MAX) = NULL,

@server SYSNAME = NULL,

@db SYSNAME = N'Scratch',

@schema SYSNAME = N'dbo',

@table SYSNAME = N'nodes'

DECLARE @cols TABLE (column_name SYSNAME);

DECLARE @sql NVARCHAR(MAX) = N'

SELECT QUOTENAME(column_name)

FROM ##SERVER##.##DB##.information_schema.columns

WHERE table_schema = ##SCHEMA##

AND table_name = ##TABLE##

';

SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(@sql,

N'##SERVER##.', COALESCE(QUOTENAME(@server) + N'.', N'')),

N'##DB##.', COALESCE(QUOTENAME(@db) + N'.', N'')),

N'##SCHEMA##', COALESCE(QUOTENAME(@schema, N''''), N'''dbo''')),

N'##TABLE##', QUOTENAME(@table, N''''));

RAISERROR ('@sql = %s', 10, 1, @sql) WITH NOWAIT; -- debug line, can be removed

INSERT INTO @cols EXEC(@sql); -- get the list of columns

SELECT @liststr = STUFF( -- comma-separated list

(SELECT ',' + column_name FROM @cols FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

1, 1, ''

);

PRINT(@liststr);

Note that there are any number of formats of server name, database name, schema name or table name that could lead to this not really working (especially given QUOTENAME's 50-char irritations) -- possibly even leading to a dreaded SQL injection attack.

Consider validating the values of @server, @db, @schema and @table against master.sys.servers, master.sys.databases, information_schema.schemata and information_schema.tables first.

Also note that there are nicer ways to build that SQL string, without resorting to REPLACE -- I use the REPLACE form for my clarity, but building the string in the old-fashioned way will be more robust.

(And seriously re-evaluate whether your app really needs to do this whole thing -- usually, when someone needs to break out the dynamics across servers or databases, the underlying design is fundamentally flawed)

HTH

J.