INFORMATION_SCHEMA.COLUMNS reference

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » INFORMATION_SCHEMA.COLUMNS reference

INFORMATION_SCHEMA.COLUMNS reference

Rate Topic

Display Mode

Topic Options

Author

Message

levsha_z

Posted Monday, August 18, 2008 1:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, Visits: 68

How do you reference INFORMATION_SCHEMA.COLUMNS
from a linked server?

When I try doing that, I get the below error message:

select * from [server_2000].dbst.information_schema.columns

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "server_2000" does not contain the table ""dbst"."information_schema"."columns"". The table either does not exist or the current user does not have permissions on that table.

Post #554543

Jack Corbett

Posted Monday, August 18, 2008 1:42 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

You need to include the user accessing the view across the linked server rights to the database and the view. A basic user does not have access to INFORMATION_SCHEMA.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #554560

levsha_z

Posted Monday, August 18, 2008 2:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, Visits: 68

Thank you!

Post #554579

RBarryYoung

Posted Monday, August 18, 2008 2:30 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

Note that this is different on SQL Server 2005 than it is on SQL Server 2000. You would probably not have the same problem on a remote linked 2005 server.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #554599

levsha_z

Posted Tuesday, August 19, 2008 8:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, September 04, 2008 9:52 AM
Points: 28, Visits: 68

I've ended up using syscolumns, which creates no problem accessing it.

Thank you!

Post #555038

Matt Slocum

Posted Thursday, January 31, 2013 7:24 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:10 PM
Points: 72, Visits: 203

I know this is an old post, but it helped me to generate the following script that works against a SQL 2000 linked server (Update DBServerName, DBName, and TableName appropriately):

SELECT SC.name FROM DBServerName.DBName.dbo.sysobjects SO
JOIN DBServerName.DBName.dbo.syscolumns SC ON SO.id = SC.id
WHERE SO.Name = 'TableName'
AND SO.Type = 'U'
ORDER BY SC.Name

Thanks!
Matt

Post #1414136

mmartin1

Posted Wednesday, April 17, 2013 10:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192, Visits: 640

INFORMATION_SCHEMA columns do not have exactly the same information as sys.columns, but it may work depending on the need. Overall, sql server 2000 just does not have all of the system views in 2005 and up.

Post #1443387

« Prev Topic | Next Topic »

Permissions