6

Is there any way to find particular column in Linked Server's database within all tables.

I guess solution lies in 

EXEC sp_columns_ex
Bridge
  • 29,818
  • 9
  • 60
  • 82

3 Answers3

8
SELECT t.name as TableName, c.name as ColumnName
FROM servernamehere.databasenamehere.sys.columns c 
INNER JOIN servernamehere.databasenamehere.sys.tables t ON c.object_id = t.object_id
WHERE c.name like '%yoursearchhere%'
d89761
  • 1,434
  • 9
  • 11
  • It Searches in System Tables...Not in User tables. –  Oct 11 '12 at 08:23
  • Thanks for catching that, I had copied and pasted what I was trying out querying the master database but you can replace that database name with the name of the one you are using. – d89761 Oct 11 '12 at 16:21
  • and to get the table name qualified with schema, join `sys.schemas` as well: [example](https://stackoverflow.com/a/48173311/1026) – Nickolay Jan 09 '18 at 17:18
1

How about this:

EXECUTE [MyLinkedServer].[MyLinkedDB].dbo.sp_executesql
   N'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ...'

And fill in the where clause depending on what you want to search for?

An alternative would be creating a view that selects from INFORMATION_SCHEMA.COLUMNS and then you query that instead.

Reference for Information_Schema.Columns

Bridge
  • 29,818
  • 9
  • 60
  • 82
1

It would depend on which database your linked server is pointing to. For example, if it is Oracle, you would use Oracle syntax, if it is SQL Server, Sql Server Syntax.

The fact that you are querying the schema through a Linked server shouldnt matter.

Diego
  • 34,802
  • 21
  • 91
  • 134