select top 10 * from Information_schema.columns : Information_schema « System Settings « SQL Server / T-SQL Tutorial






4> select top 10 * from Information_schema.columns
5> GO
TABLE_CATALOG                                                                                                                    TABLE_SCHEMA
                                                          TABLE_NAME                                                                                                                       COLUMN_NAME
                                                                                                                    ORDINAL_POSITION COLUMN_DEFAULT



















                                                                                                                                      IS_NULLABLE DATA_TYPE
                                                                           CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CH
ARACTER_SET_CATALOG                                                                                                            CHARACTER_SET_SCHEMA
                                                        CHARACTER_SET_NAME                                                                                                               COLLATION_CATAL
OG                                                                                                                COLLATION_SCHEMA
                                           COLLATION_NAME                                                                                                                   DOMAIN_CATALOG
                                                                                                     DOMAIN_SCHEMA
                              DOMAIN_NAME
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
------------------------------------------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------
-------------------------------------------------------------------------- ------------------------ ---------------------- ----------------- ----------------------- ------------- ------------------ --
------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------
------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------
----------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------
------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------
----------------------------- --------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  xserver_name
                                                                                                                                   1 NULL



















                                                                                                                                      NO          varchar
                                                                                                 30                     30              NULL                    NULL          NULL               NULL NU
LL                                                                                                                             NULL
                                                        iso_1                                                                                                                            NULL
                                                                                                                  NULL
                                           SQL_Latin1_General_CP1_CI_AS                                                                                                     NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  xdttm_ins
                                                                                                                                   2 NULL



















                                                                                                                                      NO          datetime
                                                                                               NULL                   NULL              NULL                    NULL          NULL                  3 NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  xdttm_last_in
s_upd                                                                                                                              3 NULL



















                                                                                                                                      NO          datetime
                                                                                               NULL                   NULL              NULL                    NULL          NULL                  3 NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  xfallback_dbi
d                                                                                                                                  4 NULL



















                                                                                                                                      YES         smallint
                                                                                               NULL                   NULL                 5                      10             0               NULL NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  name
                                                                                                                                   5 NULL



















                                                                                                                                      NO          varchar
                                                                                                 30                     30              NULL                    NULL          NULL               NULL NU
LL                                                                                                                             NULL
                                                        iso_1                                                                                                                            NULL
                                                                                                                  NULL
                                           SQL_Latin1_General_CP1_CI_AS                                                                                                     NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  dbid
                                                                                                                                   6 NULL



















                                                                                                                                      NO          smallint
                                                                                               NULL                   NULL                 5                      10             0               NULL NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  status
                                                                                                                                   7 NULL



















                                                                                                                                      NO          smallint
                                                                                               NULL                   NULL                 5                      10             0               NULL NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_db                                                                                                                  version
                                                                                                                                   8 NULL



















                                                                                                                                      NO          smallint
                                                                                               NULL                   NULL                 5                      10             0               NULL NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_dev                                                                                                                 xserver_name
                                                                                                                                   1 NULL



















                                                                                                                                      NO          varchar
                                                                                                 30                     30              NULL                    NULL          NULL               NULL NU
LL                                                                                                                             NULL
                                                        iso_1                                                                                                                            NULL
                                                                                                                  NULL
                                           SQL_Latin1_General_CP1_CI_AS                                                                                                     NULL
                                                                                                     NULL
                              NULL
master                                                                                                                           dbo
                                                          spt_fallback_dev                                                                                                                 xdttm_ins
                                                                                                                                   2 NULL



















                                                                                                                                      NO          datetime
                                                                                               NULL                   NULL              NULL                    NULL          NULL                  3 NU
LL                                                                                                                             NULL
                                                        NULL                                                                                                                             NULL
                                                                                                                  NULL
                                           NULL                                                                                                                             NULL
                                                                                                     NULL
                              NULL

(10 rows affected)
1>
2>








26.14.Information_schema
26.14.1.select top 10 * from Information_schema.columns
26.14.2.Information_schema.views
26.14.3.Information_schema.tables
26.14.4.System views INFORMATION_SCHEMA
26.14.5.obtain metadata about database objects.
26.14.6.SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TestTable'
26.14.7.Match a table name in tempdb.INFORMATION_SCHEMA.TABLES
26.14.8.Query tempdb.INFORMATION_SCHEMA.TABLES
26.14.9.A script that creates a summary of the tables in a database
26.14.10.Check the view existance