Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to make this block dynamic? Expand / Collapse
Author
Message
Posted Sunday, July 7, 2013 4:42 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 494, Visits: 2,052
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
Post #1471001
Posted Sunday, July 7, 2013 10:43 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
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+'''')


Post #1471033
Posted Sunday, July 7, 2013 10:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1471036
Posted Monday, July 8, 2013 6:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 65, Visits: 814
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.
Post #1471148
Posted Monday, July 8, 2013 8:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 494, Visits: 2,052
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.
Post #1471213
Posted Monday, July 8, 2013 8:27 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 494, Visits: 2,052
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?
Post #1471215
Posted Monday, July 8, 2013 8:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 494, Visits: 2,052
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.


Post #1471219
Posted Tuesday, July 9, 2013 9:16 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 494, Visits: 2,052
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!
Post #1471695
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse