Get List of Tables From Linked Server 1

[MikeBronner]

I am able to get the list of tables from my linked server in SQL2000 using the following query:

 BEGIN
	DECLARE @t_tables TABLE (table_name VARCHAR(255))
	DECLARE @s_current_table_name VARCHAR(255)
		,@s_sql AS VARCHAR(4000)

	DECLARE c_loop CURSOR FOR SELECT table_name
		FROM @t_tables

	INSERT INTO @t_tables (table_name)
	SELECT table_name
	FROM master.dbo.SYSREMOTE_TABLES (N'QODBC')

	OPEN c_loop

	FETCH NEXT FROM c_loop
	INTO @s_current_table_name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @s_sql = 'SELECT * INTO ' + LOWER(@s_current_table_name) + ' FROM QODBC...' + LOWER(@s_current_table_name)

--		EXEC @s_sql
PRINT(@s_sql)

		FETCH NEXT FROM c_loop
		INTO @s_current_table_name
	END	
END

However, this will not work in SQL2005, as the server object SYSREMOTE_TABLES no longer exists. To create the proper code, I checked the internals of the system stored procedure 'sp_tables_ex' (same as in SQL2000) which gets all the information on linked server tables. This stored procedure references 'sys.fn_remote_tables', so I tried using it:

 BEGIN
	DECLARE @t_tables TABLE (table_name VARCHAR(255))
	DECLARE @s_current_table_name VARCHAR(255)
		,@s_sql AS VARCHAR(4000)

	DECLARE c_loop CURSOR FOR SELECT table_name
		FROM @t_tables

	INSERT INTO @t_tables (table_name)
	SELECT table_name
	FROM sys.fn_remote_tables (N'QODBC')

	OPEN c_loop

	FETCH NEXT FROM c_loop
	INTO @s_current_table_name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @s_sql = 'SELECT * INTO ' + LOWER(@s_current_table_name) + ' FROM QODBC...' + LOWER(@s_current_table_name)

--		EXEC @s_sql
PRINT(@s_sql)

		FETCH NEXT FROM c_loop
		INTO @s_current_table_name
	END	
END

This didn't work, and returned the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.fn_remote_tables'.

A google search on 'fn_remote_tables' returned absolutely nothing. Is this a phantom function? I couldn't find the function in SQL server either.

Anyone know how to return the list of tables in a linked server on SQL2005, or fix the above error? Thanks!

Take Care,
Mike

 

[MikeBronner]

*bump*
No-one has run into this dilemma?

Take Care,
Mike

 

[ESquared]

Why can't you use INSERT TableName EXEC sp_tables_ex? I don't have any linked servers in SQL 2005 to test this out on, but here's code for SQL 2000:

CREATE TABLE #temp (
   TABLE_CAT sysname,
   TABLE_SCHEM sysname,
   TABLE_NAME sysname,
   TABLE_TYPE nvarchar(100),
   REMARKS nvarchar(100)
)

INSERT #temp EXEC sp_tables_ex 'medrecsql2'

SELECT * FROM #temp

DROP TABLE #temp

This is much simpler than the code you posted.

I'm sure you can do something similar in SQL 2005. Mind you, I don't know the exact data types for the table, so I just guessed. The sysnames are probably pretty sure, but don't know about table_type and remarks.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[MikeBronner]

Thanks! This worked like a charm. In all my years dealing with SQL I have always wondered how to capture the output of a stored procedure into a table!

Take Care,
Mike