I was recently working on exploring a couple of tables in a database that I was troubleshooting for performance purposes. I was using the system stored procedure sp_help and all was fine until I started accessing tables with a schema other than the default – “dbo”.
As soon as I started to access tables with schemas other than “dbo”, I encountered the following error:
USE AdventureWorks2012;
GO
sp_help HumanResources.Employee;
GO
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘.’.
Initially I thought that it was because I had not enclosed them in square brackets to indicate object identifiers, so, I tried that with the same results:
USE AdventureWorks2012;
GO
sp_help [HumanResources].[Employee];
GO
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘.’.
Then, I realized that sp_help is possibly unable to handle the multi-part naming convention of SQL Server objects, which to me is quite odd. So, I enclosed the entire two-part name inside of square brackets and it worked!
USE AdventureWorks2012;
GO
sp_help [HumanResources.Employee];
GO
And so did this:
USE AdventureWorks2012;
GO
sp_help ‘HumanResources.Employee’;
GO
As I mentioned earlier, I find this behaviour of sp_help to be strange. I can understand why it accepts [schemaname.objectname] format, but what I can’t understand is why it can’t accept [schemaname].[objectname]. While I was able to get through the exploratory process with my newly discovered workaround, I am quite sure that many developers would be ending up with this error day in and day out.
Have you encountered this behaviour?
Further Reading:
- An Introduction to Multi-part naming standards for Object name referencing [Link]
- Scripts to generate and parse multi-part database object names – PARSENAME() function [Link]
- sp_help [Books On Line Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
The PARSENAME function can help in procedures that must accept multipart object names and then pass them to something that likes a one-part name.
SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 1) AS ‘Object Name’; — Contact
SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 2) AS ‘Schema Name’; — Person
SELECT PARSENAME(‘AdventureWorks.Person.Contact’, 3) AS ‘Database Name’; — AdventureWorks
SELECT PARSENAME(‘Bodhi2.AdventureWorks.Person.Contact’, 4) AS ‘Server Name’;
use AdventureWorks;
exec sp_help ‘AdventureWorks.Person.Contact’; –This works
exec sp_help ‘Bodhi2CBI.AdventureWorks.Person.Contact’; — This fails. sp_help will never accept a 4-part name.
use master;
exec sp_help ‘AdventureWorks.Person.Contact’; –This fails on “The database name component of the object qualifier must be the name of the current database.”
LikeLike