December 19, 2011 at 11:03 am
Greetz!
So last night I was all excited about finding the sp_help command. I ran it in SQL Server 2008 Express edition and it gave me lots of useful info. I came to work today and fired it off on one of our machines, SQL Server 2008 R2 Enterprise, and got:
use Test_Content_DB
go
exec sp_help 'Test_Content_DB'
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Test_Content_DB' does not exist in database 'Test_Content_DB' or is invalid for this operation.
I'm in the sysadmin and public role on the instance. I don't have to add myself to every DB do I? Is there just a setting I need to alter on the instance?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
December 19, 2011 at 11:08 am
sp_help is for ibjects in a database. If you need help or info about a database, you have to sue sp_helpDB
-Roy
December 19, 2011 at 11:22 am
Some examples of sp_help.
A. Returning information about all objects
The following example lists information about each object in the master database.
USE master;
GO
EXEC sp_help;
GO
B. Returning information about a single object
The following example displays information about the Contact table.
USE AdventureWorks;
GO
EXEC sp_help 'Person.Contact';
GO
December 19, 2011 at 11:32 am
What I ran last night was sp_help 'Sample' and got 2 reulstsets back. The first contained info like db_size, id, owner, status, compatability_level. The second resultset showed infor about the files that made up the db (mdf and ldf's etc)
I should be able to run sp_help with a dataabsename (not sp_helpdb...unless thats 2008 specific..it does work though) and get similar results.
I'm wondering if it could be permission related. Undeer Security/Logins I have public and sysadmin roles assigned to my login. I'm not, however, seeing my login when I open a particular DB.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
December 19, 2011 at 11:36 am
As per definition of books online sp_help
Is the name of any object, in sysobjects or any user-defined data type in the systypes table. name is nvarchar(776), with a default of NULL. Database names are not acceptable.
-Roy
December 19, 2011 at 12:58 pm
Great! Thanks Roy. I'm still confused about how I got it to show results last night with a database but maybe that's an Express thing. I appreciate the clarification.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
December 20, 2011 at 5:12 am
MothInTheMachine (12/19/2011)
Great! Thanks Roy. I'm still confused about how I got it to show results last night with a database but maybe that's an Express thing. I appreciate the clarification.
sp_help stored procedure’s behaviour doesn't change per server edition. Try the same command on old system (where you got DB information) once again, I bet it would work as Roy has already explained here.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply