Sql-server – How to get list of all tables in SQL Server without specifying a db

dynamic-sqlsql server

I have a function that allows me to check if a table name is valid, that looks like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tbl

But this has to be executed in the context of the database within which you are checking. I would like to make this dynamic. i.e.

DECLARE @db VARCHAR(250) = @dbString;
DECLARE @tbl VARCHAR(250) = @tblString;
RETURN (
    SELECT TABLE_NAME
    FROM @db.INFORMATION_SCHEMA.TABLES // this is the line!
    WHERE TABLE_NAME = @tbl
)

But I have found that I can't execute dynamic SQL from within a function. This function is called from within an SP.

Alternatively, is it possible to get a return value from an SP when that SP is executed from within an SP?

I have found that this query:

select * from master.sys.databases

gives me a list of databases. Is there anything I can join this to to get a list of tables?

Other solutions that I have found all rely on executing an SP

Best Answer

There is very little metadata you can get across databases. OBJECT_ID works, so you can see if an object with a specified name exists in a specified database, but that's about it. EG:

select object_id('adventureworksdw2014.dbo.DimDate')

You can alternatively use a stored procedure with dynamic SQL, something like:

create or alter procedure table_exists @database sysname, @tablename sysname, @exists bit out
as
begin
   declare @sql nvarchar(max) = concat('select @exists=1 from ',@database,'.sys.tables where name = ''',@tablename,'''')
   exec sp_executesql @sql, N'@exists bit out', @exists  = @exists out
end
go

declare @exists bit
exec table_exists 'AdventureWorksDW2014','DimDate', @exists out 

select @exists

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH

Sql-server – Difficulty getting sp_executesql to work

I think you are attempting to get a list of tables that contain rows referenced in another table. I think you want to encapsulate this into a re-usable object, such as a stored procedure.

I created a simple schema to test your code, as such:

USE TempDB;
CREATE TABLE dbo.T1
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
    ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
    , SomeOtherData VARCHAR(255)
);

INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');

The following shows the table T2 as output, which I think is what you are attempting to do.

CREATE PROCEDURE dbo.GetReferencedTables
(
    @TableName SYSNAME
    , @RowID INT
)
AS
BEGIN
    DECLARE @OutputTable TABLE
    (
            TableName SYSNAME
    );
    DECLARE @Command nvarchar(max);

    SELECT @Command = isnull(@Command + ' union all ', '') + '
        SELECT ''' + object_name(parent_object_id) + ''' 
        WHERE EXISTS(SELECT * FROM ' + object_name(parent_object_id) 
        + ' WHERE ' + col.name+ ' = ' + cast(@RowId as varchar) + ')' 
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns col ON
            fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
    WHERE OBJECT_NAME(referenced_object_id) = @TableName;

    --Totally unnecessary to put the results into a table variable
    --unless you want to further manipulate or use the results
    INSERT INTO @OutputTable
    EXEC sp_executesql @Command;

    SELECT * /* NEVER use SELECT * unless you're me. 
            see http://bit.ly/1nRziYq for reasons.
            */
    FROM @OutputTable
    ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;

Output:

enter image description here

Best Answer

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

Sql-server – Difficulty getting sp_executesql to work

Related Question