CODEWITHSUNDEEP
sql-serverstored-proceduresssmsadventureworks
Francesco Mantovani
Francesco Mantovani

Reputation: 12357

sp_column not considering @Column_name

I'm reading the official Microsoft guide about sp_columns and I'm trying to retrieve only one specific column from AdventureWorks2016.dbo.Person.Address: 

exec sp_columns @table_name = 'Person.Address', @Column_name = 'NULLABLE'

SSMS is retrieving all all columns as empty

enter image description here

The Microsoft documentation says I can use @Column_name

Upvotes: 0

Views: 255

Answers (2)

rami
rami

Reputation: 76

If you execute this: 

exec sp_columns @table_name = 'Address'

You should get the following:

enter image description here

How ever the @Column_name = 'NULLABLE' does not exist in the table

enter image description here

And if you run the query with the correct name of the column you will get it working.

For example this:

exec sp_columns @table_name = 'Address', @Column_name = 'AddressID'

enter image description here

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46241

The @table_name parameter value must be the table name alone. Specify the schema name separately using the @table_owner column and specify the name of an existing column with the @column_name parameter value:

exec sp_columns @table_name = N'Address', @table_owner = N'Person', @column_name = N'AddressLine1';

Schema and owner are conceptually different in modern SQL Server versions but this is a rather old stored procedure where schema and table owner are used interchangeably.

Upvotes: 1

Related Questions