How to join sys.databases, sys.tables and sys.columns

user_4662682 0
Viewed 11450

I have to check for value existence in a subset of tables in a subset of databases of a sql server instance. Beware I need to do this because I have 30 databases with same schema name and similar structure. Querying all databases separately is a waste of time.

The query generates correctly code for existing tables, but the additional check for column existence in table fails. The column in some tables does not exist so the generated code must not include queries on tables without this column.

To solve this I need to realiably find a way to join sys.databases with sys.tables and then sys.columns. Or an alternative way to query all the required databases in a time saving manner.

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      exist INT
    , DB VARCHAR(50)
    , tbname VARCHAR(500)
)

/*tables common root, 
all tables i need to query start with this prefix and a number between 1 and 50
and some resulting tables do not exist
ex: dbo.Z_WBL_ASCHEDA23 exist in wbcto, while dbo.Z_WBL_ASCHEDA23 does not exist in db wbgtg    
*/

DECLARE @TableName NVARCHAR(200) 
SELECT @TableName = 'dbo.Z_WBL_ASCHEDA'

DECLARE @SQL NVARCHAR(MAX)

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 50
)
SELECT @SQL = STUFF((
    SELECT CHAR(13)+'SELECT COUNT(1), ''' + db.name + ''', '''+
        @TableName+CONVERT(VARCHAR, n.n)+'''  FROM ' +@TableName+CONVERT(VARCHAR, n.n) 
        + ' WHERE COALESCE(s_dettagli,'''') = ''CONTROLLATO'' '
        +CHAR(13)
    FROM sys.databases db
    INNER JOIN n ON 1=1
    INNER JOIN sys.tables t ON OBJECT_ID(db.name + '.' + @TableName+CONVERT(VARCHAR, n.n)) IS NOT NULL
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID and c.name = 's_dettagli' 
    /*join on columns not working, generates sql for tables without 's_dettagli' column and query fails*/
    WHERE db.name like 'wb%' --check only databases starting with 'wb'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

select @SQL

INSERT INTO #temp (exist, DB, tbname)              
EXEC sys.sp_executesql @SQL

SELECT * 
FROM #temp t
where exist <> 0

EDIT: adding some sql generated from query

SELECT COUNT(1), 'wb360', 'dbo.Z_WBL_ASCHEDA23'  FROM wb360.dbo.Z_WBL_ASCHEDA23 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'  
SELECT COUNT(1), 'Wbbim', 'dbo.Z_WBL_ASCHEDA32'  FROM Wbbim.dbo.Z_WBL_ASCHEDA32 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'

the table of first query doesn't contain 's_dettagli' column

EDIT2: SOLUTION

EXEC sp_MSforeachdb '
IF ''?'' not like ''wb%'' 
    RETURN
USE [?]
EXEC sp_MSforeachtable 
@replacechar = ''!'', 
@command1 = ''SELECT ''''?'''' AS db_name, ''''!'''' AS table_name, COUNT(*) FROM !  '',
@whereand = '' And Object_id In (
                                Select t.Object_id 
                                From sys.objects t
                                INNER JOIN sys.columns c on c.Object_id = t.Object_id
                                Where t.name like ''''Z_WBL_ASCHEDA%''''
                                AND c.name = ''''s_dettagli'''' )'' '
sql-server
3 Answers

Sys.columns can be joined to sys.tables using the object_id field (the object_id is the representation of the table itself).

sys.tables is run in the context of the database you are querying, hence you cannot see a table contained in another database. sys.databases can be run on any database on an instance and allow you to view other databases on the same instance. As such you don't need to join the table to the database (also the reason why there is no database_id field within sys.tables).

I hope that helps. Any clarification please let me know.

user_83358970

I would suggest alternative ways:

  1. use registered Servers in SSMS and run the script on each database here
  2. use exec sys.sp_MSforeachdb here
  3. use sqlcmd and powershell to switch databases
user_59749650

I believe this script can help you :

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#Temp') IS NOT NULL
   DROP TABLE #Temp

CREATE TABLE #Temp
(
      exist INT
    , DB VARCHAR(50)
    , tbname VARCHAR(500)
)

DECLARE @SchemaName      NVARCHAR(200) 
DECLARE @TableName       NVARCHAR(200) 
DECLARE @ColumnName      NVARCHAR(200) 
DECLARE @SearchText      NVARCHAR(200) 
DECLARE @DBNameStartWith NVARCHAR(200) 
DECLARE @SQL             NVARCHAR(MAX)


SET @DBNameStartWith = 'wb'
SET @SchemaName      = 'dbo'
SET @TableName       = 'Z_WBL_ASCHEDA'
SET @ColumnName      = 's_dettagli'
SET @SearchText      = 'CONTROLLATO'


DECLARE @DatabaseName varchar(100) 

DECLARE Crsr CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases 
WHERE name LIKE ''+@DBNameStartWith+'%'

OPEN Crsr 
FETCH NEXT FROM Crsr INTO @DatabaseName 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     IF ISNULL((SELECT COUNT(1) FROM SYS.TABLES T,SYS.COLUMNS C WHERE T.object_id=C.object_id AND T.name=@TableName AND C.name=@ColumnName),0)>0
     BEGIN
          SET  @SQL = '
               IF EXISTS (SELECT 1 FROM '+@DatabaseName+'.SYS.TABLES T,'+@DatabaseName+'.SYS.COLUMNS C WHERE T.object_id=C.object_id AND T.name='''+@TableName+''' AND C.name='''+@ColumnName+''')
               BEGIN          
                    SELECT COUNT(1),'''+@DatabaseName+''','''+@TableName+''' 
                    FROM '+@DatabaseName+'.'+@SchemaName+'.'+@TableName+'
                    WHERE '+@ColumnName+'=''' +@SearchText+'''
               END'

          PRINT(@SQL)
          INSERT INTO #Temp
          EXEC sp_executesql @SQL 
     END
     FETCH NEXT FROM Crsr INTO @DatabaseName 
END 
CLOSE Crsr 
DEALLOCATE Crsr 

SELECT * FROM #Temp
user_105848250
Related