December 17, 2008 at 4:48 pm
I have a script to pull info from every database and am using this stored procedure. It works great for most of my servers with a couple of exceptions. I have one server with an exceptionally long database name and a development server that has a database name with a blank in it. This script errors out - the parameter (database name) is being truncated.
EXEC sp_MSforeachdb
'USE ?
SELECT Table_Catalog,Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS'
If I put print statements in before the 'USE', it prints the entire database name. The ? in the 'USE' statement only executes with a partial database name and causes an error.
Has anyone seen this or have a solution?
Thanks,
Kim Talley
December 18, 2008 at 3:53 am
In case you have blanks in your database name you have to use parenthesis around the database name.
EXEC sp_MSforeachdb
'USE [?]
SELECT Table_Catalog,Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS'
[font="Verdana"]Markus Bohse[/font]
December 18, 2008 at 6:59 am
sp_msforeachdb will handle database names with a length of up to 128 characters, you can't create databases with longer names, so not sure what the issue is with the long one.
December 18, 2008 at 7:42 am
Thanks for the help! The brackets around the ? fixed my problem. I think the other issue was not that the name was long, but that there was a dash in the name?? Not sure, but the brackets fixed both.
Thanks again,
Kim
February 2, 2012 at 9:27 am
Thanks for the tip MarkusB, your bracket solution fixed my problem as well, sharepoint has dashes in some of the database names.
February 2, 2012 at 9:34 am
If you want to execute multiple queries against all databases then check the following solution
http://saveadba.blogspot.com/2011/10/sql-server-execute-same-query-against.html
Blog
http://saveadba.blogspot.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply