DECLARE @DBList VARCHAR(MAX)
SELECT @DBList = COALESCE(@DBList + ''',''', '') + name
FROM sys.databases
ORDER BY name
SELECT @DBList
But that still leaves the beginning and ending quotes. Perhaps this might be simpler.
DECLARE @DBList VARCHAR(MAX)
SELECT @DBList =(
SELECT STUFF
(
(SELECT ',''' + name + '''' FROM sys.databases ORDER BY name FOR XML PATH('')
),1,1,''));
SELECT @dblist;
Might be worth you explaining what the underlying problem is that you are working to solve. Creating a delimited list and using IN, and thus using Dynamic SQL, rarely has a problem for which it is the the best solution
Can you not, for example, do:
SELECT A, B, C, ...
FROM MyTable
WHERE MyColumn IN
(
SELECT name
FROM sys.databases
)
I wanted to learn how to do it the other way though, because after a few min, I got frustrated of not being able to add the starting single quote, lol.
I have not tested it, but yours maybe less overkill.
The initial suggestion or the use of single quotes came from a modification I made of Ola Hallengren code, which requires singles quotes for the dblist.
There are other issues using dynamic SQL with a delimited list.
What happens if a database name has a single-quote in it? Unlikely, I know, but can happen ...
if you broaden this to also use Dynamic SQL for stuff where a User provides some data / parameters etc. then there is the whole SQL Injection can-of-worms to consider ...
But IME it is common for folk to propose a solution to a problem and ask for help to make that solution work, rather than to describe the problem and seek suggestions as to the best solution ...
Using the later approach, and assuming you get several replies with different solution, is more likely to broaden ones' knowledge.