SQL Server/T-SQL Tutorial/System Settings/Information schema
Содержание
- 1 A script that creates a summary of the tables in a database
- 2 Check the view existance
- 3 Information_schema.tables
- 4 Information_schema.views
- 5 Match a table name in tempdb.INFORMATION_SCHEMA.TABLES
- 6 obtain metadata about database objects.
- 7 Query tempdb.INFORMATION_SCHEMA.TABLES
- 8 SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "TestTable"
- 9 select top 10 * from Information_schema.columns
- 10 System views INFORMATION_SCHEMA
A script that creates a summary of the tables in a database
8> SELECT TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName,
9> DATA_TYPE AS Type
10> INTO #TableSummary
11> FROM INFORMATION_SCHEMA.COLUMNS
12> WHERE TABLE_NAME IN
13> (SELECT TABLE_NAME
14> FROM INFORMATION_SCHEMA.TABLES
15> WHERE (TABLE_TYPE = "BASE TABLE" AND
16> TABLE_NAME NOT IN ("dtproperties", "TableSummary", "AllUserTables")))
17>
18> CREATE TABLE #AllUserTables
19> (TableID int IDENTITY, TableName varchar(128))
20> GO
1>
2> INSERT #AllUserTables (TableName)
3> SELECT TABLE_NAME
4> FROM INFORMATION_SCHEMA.TABLES
5> WHERE (TABLE_TYPE = "BASE TABLE" AND
6> TABLE_NAME NOT IN ("dtproperties", "TableSummary", "AllUserTables"))
7> GO
(17 rows affected)
1>
2> DECLARE @LoopMax int, @LoopVar int
3> DECLARE @TableNameVar varchar(128), @ExecVar varchar(1000)
4>
5> SELECT @LoopMax = MAX(TableID) FROM #AllUserTables
6>
7> SET @LoopVar = 1
8>
9> WHILE @LoopVar <= @LoopMax
10> BEGIN
11> SELECT @TableNameVar = TableName
12> FROM #AllUserTables
13> WHERE TableID = @LoopVar
14> SET @ExecVar = "DECLARE @CountVar int "
15> SET @ExecVar = @ExecVar + "SELECT @CountVar = COUNT(*) "
16> SET @ExecVar = @ExecVar + "FROM " + @TableNameVar + " "
17> SET @ExecVar = @ExecVar + "INSERT #TableSummary "
18> SET @ExecVar = @ExecVar + "VALUES (""" + @TableNameVar + ""","
19> SET @ExecVar = @ExecVar + """*Row Count*"","
20> SET @ExecVar = @ExecVar + " @CountVar)"
21> EXEC (@ExecVar)
22> SET @LoopVar = @LoopVar + 1
23> END
24> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT * FROM #TableSummary
3> ORDER BY TableName, ColumnName
4> GO
TableName ColumnName
Type
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
authors_CS *Row Count*
10
authors_CS address
varchar
authors_CS au_fname
varchar
authors_CS au_id
varchar
authors_CS au_lname
varchar
authors_CS city
varchar
authors_CS contract
bit
authors_CS phone
char
authors_CS state
char
authors_CS zip
char
BillingCopy *Row Count*
0
BillingCopy CreditTotal
int
BillingCopy BillingDate
datetime
BillingCopy BillingDueDate
datetime
BillingCopy BillingNumber
int
BillingCopy BillingTotal
int
BillingCopy PaymentTotal
int
BillingCopy TermsID
int
BillingCopy BankerID
int
JaviersData *Row Count*
0
JaviersData SomeColumn
int
MSreplication_options *Row Count*
3
MSreplication_options install_failures
int
MSreplication_options major_version
int
MSreplication_options minor_version
int
MSreplication_options optname
nvarchar
MSreplication_options revision
int
MSreplication_options value
bit
OldBillings *Row Count*
0
OldBillings CreditTotal
int
OldBillings BillingDate
datetime
OldBillings BillingDueDate
datetime
OldBillings BillingNumber
int
OldBillings BillingTotal
int
OldBillings PaymentTotal
int
OldBillings TermsID
int
OldBillings BankerID
int
department_pivot *Row Count*
11
department_pivot budget
float
department_pivot date_month
datetime
department_pivot dept_name
char
department_pivot emp_cnt
int
department_pivot month
int
department_pivot year
int
pub_info *Row Count*
0
pub_info logo
image
pub_info pr_info
text
pub_info pub_id
char
SalesMw *Row Count*
13
SalesMw CD_ID
int
SalesMw QtySold
int
SalesMw SalesDate
datetime
SalesMw StoreID
int
spt_fallback_db *Row Count*
0
spt_fallback_db dbid
smallint
spt_fallback_db name
varchar
spt_fallback_db status
smallint
spt_fallback_db version
smallint
spt_fallback_db xdttm_ins
datetime
spt_fallback_db xdttm_last_ins_upd
datetime
spt_fallback_db xfallback_dbid
smallint
spt_fallback_db xserver_name
varchar
spt_fallback_dev *Row Count*
0
spt_fallback_dev high
int
spt_fallback_dev low
int
spt_fallback_dev name
varchar
spt_fallback_dev phyname
varchar
spt_fallback_dev status
smallint
spt_fallback_dev xdttm_ins
datetime
spt_fallback_dev xdttm_last_ins_upd
datetime
spt_fallback_dev xfallback_drive
char
spt_fallback_dev xfallback_low
int
spt_fallback_dev xserver_name
varchar
spt_fallback_usg *Row Count*
0
spt_fallback_usg dbid
smallint
spt_fallback_usg lstart
int
spt_fallback_usg segmap
int
spt_fallback_usg sizepg
int
spt_fallback_usg vstart
int
spt_fallback_usg xdttm_ins
datetime
spt_fallback_usg xdttm_last_ins_upd
datetime
spt_fallback_usg xfallback_vstart
int
spt_fallback_usg xserver_name
varchar
spt_monitor *Row Count*
1
spt_monitor connections
int
spt_monitor cpu_busy
int
spt_monitor idle
int
spt_monitor io_busy
int
spt_monitor lastrun
datetime
spt_monitor pack_errors
int
spt_monitor pack_received
int
spt_monitor pack_sent
int
spt_monitor total_errors
int
spt_monitor total_read
int
spt_monitor total_write
int
spt_values *Row Count*
2346
spt_values high
int
spt_values low
int
spt_values name
nvarchar
spt_values number
int
spt_values status
int
spt_values type
nchar
Table1 *Row Count*
4
Table1 a
int
Table1 b
datetime
Table1 c
varchar
Table2 *Row Count*
12
Table2 a
int
Table2 message
varchar
titleauthor *Row Count*
24
titleauthor au_id
varchar
titleauthor au_ord
tinyint
titleauthor royaltyper
int
titleauthor title_id
varchar
BankerBalances *Row Count*
10
BankerBalances SumOfBillings
int
BankerBalances BankerID
int
(117 rows affected)
Check the view existance
3>
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> IF EXISTS (SELECT TABLE_NAME
5> FROM INFORMATION_SCHEMA.VIEWS
6> WHERE TABLE_NAME = "vEmployee")
7> DROP VIEW vEmployee
8> GO
1> CREATE VIEW vEmployee WITH SCHEMABINDING
2> AS
3> SELECT Id, first_name FROM dbo.Employee
4> GO
1>
2> select * from vEmployee
3> GO
Id first_name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
6 Linda
7 David
8 James
9 Joan
(9 rows affected)
1>
2> drop view vEmployee
3> drop table employee;
4> GO
Information_schema.tables
The Information_schema. tables view contains one row for each table in the current database to which the user has access.
13>
14> select top 10 * from Information_schema.tables;
15> GO
TABLE_CATALOG TABLE_SCHEMA
TABLE_NAME VIEW_DEFINITI
ON
CHECK_OPTION
IS_UPDATABLE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------
------------
(0 rows affected)
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)
1>
Information_schema.views
The Information_schema.views view contains one row for each view in the current database accessible by the user.
7>
8> select top 10 * from Information_schema.views;
9>
Match a table name in tempdb.INFORMATION_SCHEMA.TABLES
5> SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
6> WHERE TABLE_NAME LIKE "%#%";
7> GO
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
#4460231C
#4C0144E4
#T42________________________________________________________________________________________________________________000000000019
#73DA2C14
#7F4BDEC0
1>
2>
obtain metadata about database objects.
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
Query tempdb.INFORMATION_SCHEMA.TABLES
6> DECLARE @T TABLE(col1 INT);
7> INSERT INTO @T VALUES(1);
8> SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
9> WHERE TABLE_NAME LIKE "%#%";
10> GO
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
#4460231C
#4C0144E4
#T42________________________________________________________________________________________________________________000000000019
#642DD430
#73DA2C14
#7F4BDEC0
1>
2>
SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "TestTable"
5> CREATE DATABASE Test
6> GO
1> CREATE TABLE TestTable(
2> col1 int,
3> col2 int
4> )
5> GO
1> SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "TestTable"
2> GO
TABLE_CATALOG
--------------------------------------------------------------------------------------------------------------------------------
master
(1 rows affected)
1>
2> drop table TestTable;
3> GO
1>
2> drop database Test;
3> GO
select top 10 * from Information_schema.columns
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>
System views INFORMATION_SCHEMA
3>
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2> IF EXISTS (
3> SELECT * FROM Information_Schema.Tables WHERE Table_Name = "Employee"
4> )
5> DROP TABLE Employee
6> GO