#0316 – SQL Server – sp_help and multi-part naming of objects – Msg 102 – Incorrect syntax near ‘.’


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

image


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.

1 thought on “#0316 – SQL Server – sp_help and multi-part naming of objects – Msg 102 – Incorrect syntax near ‘.’

  1. Bodhi2

    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.”

    Like

    Reply

Leave a Reply to Bodhi2 Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.