SQL Server/T-SQL/System/INFORMATION SCHEMA

Another way to test for the existence of a table

 

4>
5> create table Billings (
6>     BankerID           INTEGER,
7>     BillingNumber      INTEGER,
8>     BillingDate        datetime,
9>     BillingTotal       INTEGER,
10>     TermsID            INTEGER,
11>     BillingDueDate     datetime ,
12>     PaymentTotal       INTEGER,
13>     CreditTotal        INTEGER
14>
15> );
16> GO
1>
2>
3> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
4>         WHERE (TABLE_NAME = "Billing" AND
5>         TABLE_TYPE = "BASE TABLE"))
6>     DROP TABLE Billing
7> GO
1>
2> drop table Billings;
3> GO

INFORMATION_SCHEMA.TABLES

 

7> SELECT top 10 * FROM INFORMATION_SCHEMA.TABLES
8> GO
TABLE_CATALOG                                                                                                                    TABLE_SCHEMA
                                                          TABLE_NAME                                                                                                                       TABLE_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  BASE TABLE
master                                                                                                                           dbo
                                                          spt_fallback_dev                                                                                                                 BASE TABLE
master                                                                                                                           dbo
                                                          spt_fallback_usg                                                                                                                 BASE TABLE
master                                                                                                                           dbo
                                                          airplane                                                                                                                         BASE TABLE
master                                                                                                                           dbo
                                                          spt_monitor                                                                                                                      BASE TABLE
master                                                                                                                           dbo
                                                          spt_values                                                                                                                       BASE TABLE
master                                                                                                                           dbo
                                                          MSreplication_options                                                                                                            BASE TABLE
(7 rows affected)

Query INFORMATION_SCHEMA.VIEWS

 


3> SELECT top 10 TABLE_NAME, TABLE_SCHEMA
4> FROM INFORMATION_SCHEMA.VIEWS
5> GO
TABLE_NAME                                                                                                                       TABLE_SCHEMA
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
---------------------------------------------------------
titleview                                                                                                                        dbo
vwProductList                                                                                                                    dbo
vV                                                                                                                               dbo

(3 rows affected)
1>