CODEWITHSUNDEEP
sqlforeachmultipartsp-msforeachtable
Mariano Barbuscio
Mariano Barbuscio

Reputation: 21

Getting Multipart Identifier could not be bound using sp_MSforeachtable

I'm using the sp_MSForeachtable to retrieve the columnames of all tables and concatenating the columnames in a single string. I'm using the following query. I've executed the same providing the parameter through a variable for a single table and works perfectly, but when executed from the SP it fails with the error 'The multi-part identifier "dbo.TableNm" could not be bound.'

DECLARE @query nvarchar(max)
SELECT  @query = 

'DECLARE @Names VARCHAR(255)  
DECLARE @DB VARCHAR(255)

SELECT @Names = COALESCE(@Names + '', '', '''') + COLUMN_NAME FROM Information_Schema.COLUMNS
WHERE TABLE_NAME = ?

SELECT TOP 1  @DB = TABLE_CATALOG FROM Information_Schema.COLUMNS
WHERE TABLE_NAME = ?

SELECT @DB AS [DataBase], ? AS [Table], @Names AS [Columns]'

EXEC sp_MSforeachtable @query

I thought the error might be associated with having multiple tables with the same name in different databases so I tried pre-fixing the database but i still get the same error.

DECLARE @query nvarchar(max)
SELECT  @query = 

'DECLARE @Names VARCHAR(255)  
DECLARE @DB VARCHAR(255)
DECLARE @TableNm VARCHAR(255) = ?

SET @DB = ''People_Directory''

SELECT @Names = COALESCE(@Names + '', '', '''') + COLUMN_NAME FROM Information_Schema.COLUMNS
WHERE TABLE_NAME = @TableNm
AND TABLE_CATALOG = @DB

SELECT @DB AS [DataBase], @TableNm AS [Table], @Names AS [Columns]'


EXEC sp_MSforeachtable @query

I'll keep trying bu I'm running out of ideas. Any thoughts?

Upvotes: 2

Views: 904

Answers (1)

Richard Deeming
Richard Deeming

Reputation: 31198

The ? in the query will be replaced with the quoted schema-qualified name of the table. It will not be enclosed in quotes, so your query is equivalent to:

SELECT ...
WHERE TABLE_NAME = [dbo].[YourTable]
...
SELECT @DB As [DataBase], [dbo].[YourTable] As [Table], @Names As [Columns]

This will obviously generate an error.

You'll need to add the quotes around the ? so that it's treated as a string. However, your query still won't work, as the TABLE_NAME column doesn't include the schema name, and isn't quoted.

To make the query work as expected, you'll need to combine the TABLE_SCHEMA and TABLE_NAME columns, and make sure the values are quoted, before comparing to the current table name:

DECLARE @query nvarchar(max)
SELECT  @query = 

'DECLARE @Names VARCHAR(255)  
DECLARE @DB VARCHAR(255)

SELECT @Names = COALESCE(@Names + '', '', '''') + COLUMN_NAME FROM Information_Schema.COLUMNS
WHERE QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) = ''?''

SELECT TOP 1  @DB = TABLE_CATALOG FROM Information_Schema.COLUMNS
WHERE QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) = ''?''

SELECT @DB AS [DataBase], ''?'' AS [Table], @Names AS [Columns]'

EXEC sp_MSforeachtable @query

EDIT:
You don't actually need to use sp_MSforeachtable to do this. Using one of the methods from this article, you can retrieve this information in a single query:

SELECT
    T.TABLE_CATALOG As [DataBase],
    QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME) As [Table],
    STUFF(
        (
            SELECT ', ' + COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS As C
            WHERE C.TABLE_CATALOG = T.TABLE_CATALOG
            And C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
            ORDER BY C.ORDINAL_POSITION
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
    , 1, 1, '') As [Columns]
FROM
    INFORMATION_SCHEMA.TABLES As T
;

Upvotes: 0

Related Questions