July 7, 2013 at 4:42 pm
I'm just not sure how to do it.
When I run:
select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)
I get a list of column names like so:
[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...
Running this returns nothing:
select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)
I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?
Thanks
July 7, 2013 at 10:51 pm
erikd (7/7/2013)
I'm just not sure how to do it.When I run:
select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)
I get a list of column names like so:
[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...
Running this returns nothing:
select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)
I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?
Thanks
I think just setting your variable to '' will do the trick.
SET @liststr = ''
select @liststr = 'coalesce...etc
or
(' + ISNULL(@liststr,'') + '+'','' ,'''')
If @liststr is NULL the concatenation will also return NULL.
You might also consider using the FOR XML PATH method for concatentation which is often more efficient than using COALESCE.
July 8, 2013 at 6:32 am
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.
July 8, 2013 at 8:21 am
sharath.chalamgari (7/7/2013)
are you looking for something like this
exec ('select @liststr = coalesce(@liststr+'+''''+','+''''+' ,'+''''+''''+') + quotename(column_name)
from '+ @server + '.'+ @db+ '.information_schema.columns where table_name = '+ ''''+ @table+'''')
Almost:
declare @server varchar(64)
declare @db varchar(64)
declare @table varchar(64)
declare @liststr nvarchar (max)
select @server = quotename(parsename('[server].j3688802s.dbo.sample_web', 4))
select @db = parsename('[server].j3688802s.dbo.sample_web', 3)
select @table = parsename('[server].j3688802s.dbo.sample_web', 1)
print (@server)
print (@db)
print (@table)
exec ('select @liststr = coalesce(@liststr+'+''''+','+''''+' ,'+''''+''''+') + quotename(column_name)
from '+ @server + '.'+ @db+ '.information_schema.columns where table_name = '+ ''''+ @table+'''')
[server]
j3688802s
sample_web
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@liststr".
I was running in to this yesterday with a different set up, and couldn't figure out why.
July 8, 2013 at 8:27 am
Steven Willis (7/7/2013)
erikd (7/7/2013)
I'm just not sure how to do it.When I run:
select @liststr = coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample'
print (@liststr)
I get a list of column names like so:
[Id],[Queue],[PRO_PID],[PRO_FIELD1],[PRO_FIELD2],[PRO_FIELD3],[PRO_FIELD4]...
Running this returns nothing:
select @liststr = 'coalesce(' + @liststr + '+'','' ,'''') + quotename(column_name)
from ' + @server + '.' + @db + '.information_schema.columns where table_name = ''' + @table + ''''
print (@liststr)
I have all the name parts parsed correctly, I can see that in other print statements. I think I'm just processing some part of it incorrectly. Any suggestions?
Thanks
I think just setting your variable to '' will do the trick.
SET @liststr = ''
select @liststr = 'coalesce...etc
or
(' + ISNULL(@liststr,'') + '+'','' ,'''')
If @liststr is NULL the concatenation will also return NULL.
You might also consider using the FOR XML PATH method for concatentation which is often more efficient than using COALESCE.
This is close as well, but here's the result:
[server]
j3688802s
sample_web
coalesce(@liststr+',' ,'') + quotename(column_name)
from [server].j3688802s.information_schema.columns where table_name = 'sample_web'
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'coalesce'.
Also, the I've experimented with FOR XML, but it often truncates the list after so many characters. I think it's less than the 8k that normally get displayed?
July 8, 2013 at 8:34 am
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.
Thanks. I'll try to work this in to what I'm doing. For background, this is the rest of the SP:
http://www.sqlservercentral.com/Forums/Topic1467819-338-1.aspx
I found that if I tried to use it FROM different server than the one I'm using, it wouldn't give me the column results because it was looking in the completely wrong spot for them. So, it might be flawed and stupid and ugly and its mother may dress it funny, but I have to do it a lot, an I'm really just trying to make the best of it.
July 9, 2013 at 9:16 am
I got it to work by doing this:
declare @sql nvarchar (max)
declare @server varchar(64)
declare @db varchar(64)
declare @table varchar(64)
select @server = quotename(parsename('[server].j3688802s.dbo.sample_web', 4))
select @db = parsename('[server].j3688802s.dbo.sample_web', 3)
select @table = parsename('[server].j3688802s.dbo.sample_web', 1)
print (@server)
print (@db)
print (@table)
set @sql = 'Select Stuff((SELECT '', '' + quotename(column_name) AS [text()]
FROM
(SELECT column_name FROM ' + @server + '.' + @db + '.information_schema.columns
where table_name = ''' + @table + '''
) x
For XML PATH ('''')),1,1,'''')'
print (@sql)
exec sp_executesql @sql, N'@sql nvarchar(max)', @sql print (@sql)
I've never used sp_executesql before. So, I'll experiment with FOR XML, until I run into it truncating column lists.
Thanks guys!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply