9

If it's a regular database, i can simply use this query to get a list of all table names and their column names of the database.

use [my_database_name]
GO

SELECT sys.tables.name AS Table_Name, 
       sys.columns.name AS Column_Name, 
       sys.columns.max_length, 
       (schema_id) As Schema_name

FROM sys.tables
    INNER JOIN sys.columns 
        ON sys.tables.OBJECT_ID=sys.columns.object_id

ORDER BY schema_name, sys.tables.name, sys.columns.name

but right now I need to connect to a linked-server database therefore the 'use' can't be used. Is there another way?

user3486647
  • 191
  • 1
  • 4
  • 12
  • 4
    Fully qualify your tables in the select statement. `[linkedserver].[databasename].[dbo].[tablename]` – SS_DBA Dec 01 '16 at 18:17
  • But you should alias your tables. 3 (and 4) part naming in the select list is deprecated. – Sean Lange Dec 01 '16 at 18:51
  • The purpose is to list out EVERY column name of EVERY table of one linked-server database at once. WEI_DBA could you be more specific about your answer? – user3486647 Dec 01 '16 at 18:58
  • What @WEI_DBA is stating is you need to fully qualify the sys tables for your linked server like so.... `FROM [SERVER].[DATABASE].[sys].[tables] as ls` – S3S Dec 01 '16 at 19:11

4 Answers4

7

The system stored procedure sp_tables is used to list out the tables available in the current database of the current server. You can use sp_tables_ex for the linked server. The following returns list of tables available in the specified Server:

EXEC sp_tables_ex @table_server = 'MYSQL_DB'
vadim-info
  • 71
  • 1
  • 1
6

To expand slightly on @scsimon's answer...

The (schema_id) As Schema_name is not very useful, as it's really an ID. To get the list of all tables (and their columns) with actual schema names one can use:

SELECT s.name AS schema_name
      ,t.name AS table_Name
      ,c.name AS column_Name
    --,c.max_length
FROM [SERVER].[DB].sys.tables t
JOIN [SERVER].[DB].sys.schemas s ON t.schema_id = s.schema_id
JOIN [SERVER].[DB].sys.columns c ON t.object_id = c.object_id
--WHERE s.name = 'dbo'
ORDER BY s.name, t.name, c.name
Nickolay
  • 31,095
  • 13
  • 107
  • 185
3

Fully qualify your linked server in your FROM and JOIN, and alias them.

SELECT lst.name AS Table_Name, 
       lsc.name AS Column_Name, 
       lsc.max_length, 
       (schema_id) As Schema_name

FROM [SERVER].[DB].[sys].[tables] lst
    INNER JOIN [SERVER].[DB].[sys].[columns] lsc
        ON lst.OBJECT_ID=lsc.object_id

ORDER BY schema_name, lst.name, lsc.name
S3S
  • 24,809
  • 5
  • 26
  • 45
  • thanks, but i got this message: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "FBSample_test_ODBC" reported an error. The provider did not give any information about the error. Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "FBSample_test_ODBC". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema. – user3486647 Dec 01 '16 at 19:22
  • I unchecked the 'Level zero only' in the MSDASQL property but still the same error. – user3486647 Dec 01 '16 at 19:40
  • @user3486647 I'm guessing 1 of 2 things... 1 you are running the sql service as a less privileged account, or 2 since you are using an ODBC connector you'll have to use `OPENQUERY`. Check out this vide....https://youtu.be/BtyMQsE8pvY – S3S Dec 01 '16 at 19:40
  • Seriously long overdue comment...but, when I had to start working with some legacy systems and using the MSDASQL for Linked Servers, I use SELECT * FROM [Server]...[Table] (not the fully qualified [Server].[Database].[Schema].[Table]. Anyhow, maybe will help someone in the future! – Briana Finney Jan 28 '21 at 00:48
1

There is the solution to list:

declare @temp table
(
    col1 varchar(255),
    col2 varchar(255),
    [name] varchar(255),
    [type] varchar(255),
    col3 varchar(255)
)
insert @temp exec sp_tables_ex 'Your_LinkedServer_Name'
select * from @temp

And also open a cursor:

DECLARE lstTables CURSOR FOR 
        select [name] from @temp
Oxana N
  • 11
  • 3