CODEWITHSUNDEEP
sql-servert-sql
Mitux447
Mitux447

Reputation: 1

Get all tables in SQL Server database except some selected ones?

I'm looking to display names of all tables in mySQL Server database with the exception of three selected ones. In MySQL I used a Query like:

SHOW TABLES FROM " + self.databaseName + " WHERE tables_in_" + self.databaseName + " NOT LIKE 'shots' AND tables_in_" + self.databaseName + " NOT LIKE 'conditions' AND tables_in_" + self.databaseName + " NOT LIKE 'analysis'"

Is there an equivalent in SQL Server?

Upvotes: 0

Views: 932

Answers (1)

Thom A
Thom A

Reputation: 95588

Use the system tables:

SELECT s.[name] AS SchemaName,
       t.[name] AS TableName
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE t.[name] NOT IN (N'Table1',N'Table2','Table3');

You can also use the INFORMATION_SCHEMA objects, but the documentation does suggest you use the sys objects:

SELECT T.TABLE_SCHEMA,
       T.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME NOT IN (N'Table1',N'Table2','Table3')
  AND T.TABLE_TYPE = N'BASE TABLE';

Note, that if the tables you want to omit are in specific schemas and you have objects with the same name on different schemas you don't want to exclude, you would need to use AND clauses in the WHERE:

WHERE NOT(s.[name] = N'dbo' AND t.[name] = N'Table1')
  AND NOT(s.[name] = N'test' AND t.[name] = N'Table3')
  AND NOT(s.[name] = N'usr' AND t.[name] = N'Table2')

Upvotes: 1

Related Questions