1

In the code below, I am able to specify the table name and schema that I want to query with Table name and Table schema. How do I specify the database name and server name that I want to query?

I have tried DATABASE_NAME, DB_NAME and SERVER_NAME. None of them work.

 SELECT COUNT(1) AS TABLECOUNT FROM INFORMATION_SCHEMA.TABLES WHERE 
 TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '[TABLE_NAME]
Joe Joe Joe
  • 79
  • 1
  • 15

3 Answers3

3

You need to use TABLE_CATALOG for passing database name. In SQL Server this column contains the database name that contains that table as shown below.

SELECT COUNT(1) AS TABLECOUNT FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CALCULATION_SCHEDULE'
AND TABLE_CATALOG = '<Your Database Name>'

You can also pass directly server name and database name as shown below.

SELECT * FROM [ServerName].[DatabaseName].INFORMATION_SCHEMA.TABLES where ---.
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • 1
    Thank you, exactly what I was looking for. What about to specify a server name? – Joe Joe Joe Jan 28 '19 at 10:50
  • @Suraj Kumar, worth to mention that it will work only for tables that are present in a current database, such select will not check existence of objects from other database – Alexander Volok Jan 28 '19 at 10:56
  • @AlexanderVolok We can use directly server name and database also. See my answer update. I have already checked that. – Suraj Kumar Jan 28 '19 at 11:31
3

Here is how you can select database, table and server-name : 

SELECT COUNT(1) AS TABLECOUNT, @@SERVERNAME as 'serverName' FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CALCULATION_SCHEDULE' 
and @@SERVERNAME ='xx' and TABLE_CATALOG ='database name'

click here to know more about the serverName

sri harsha
  • 676
  • 6
  • 16
Alen.Toma
  • 4,684
  • 2
  • 14
  • 31
2

There is no table or view that enumerates all tablesof all databases, you have to explicitly specify database:

  1. Directly in query

    SELECT COUNT(1) AS TABLECOUNT
FROM [dbname].INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'CALCULATION_SCHEDULE'   ;

  2. Via "USE"

    USE [dbname]
SELECT COUNT(1) AS TABLECOUNT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'CALCULATION_SCHEDULE'   ;

  3. In your connection string

Regarding a Server Name, it is possible only via system variables like @@SERVERNAME or SERVERPROPERTY('ServerName'):

WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'CALCULATION_SCHEDULE'   
AND SERVERPROPERTY('ServerName') = 'YourServer' ;
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • We can use directly server name and database also. See my answer update. I have already checked that. – Suraj Kumar Jan 28 '19 at 11:31
  • @SurajKumar, of course catalog_name can be used, but it always contain a name of the database where it resides, so it has limited usefulnes. The same can be achieved with DB_NAME() = '' in case of OP – Alexander Volok Jan 28 '19 at 13:50