SQL Server Metadata

http://www.devart.com/dotconnect/sqlserver/docs/MetaData.html
https://msdn.microsoft.com/en-us/library/ms254969.aspx  SQL Server Schema Collections

In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.
GetSchema Method Reference
The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. When calling the GetSchema method, you can pass all or few arguments. In the latter case, some default values are assumed, if they were not specified explicitly. The default value of database restriction is , table restriction is base table (.NET2) or table (.NET1). Some collections are not supported in older server versions. If you try ro get metadata for unsupported collection you will get exception with message "Collection not defined".

 


Collection Name
Number of restrictions
Remarks
Arguments
2

Returns list of arguments for stored procedures and functions on the server.
Restrict it by database name and procedure name.
Columns
3

Returns list of columns, their type and some extra information.
Restricted by database name, GetSchema returns columns of table "user" if it can be found in the database.
The second restriction is name of a table that GetSchema method should search in.
At last, you can set column name pattern as described in "Tables" collection.

 

DatasourceInformation
0

Returns information about the data source.
DataTypes
0

Returns information about data types supported by the data source.
ForeignKeyColumns
2

Returns list of columns used by foreign keys in the database.
Restrict it with database name and table name.
ForeignKeys
3

Returns list of columns that participate in foreign keys.
The first restriction for this collection is name of a database.
The second restriction is table name mask.
The third restriction is the key pattern.

 

Functions
2

Returns list of stored functions on the server.
Restrict it by database name and function name.
IndexColumns
4

Returns list of indexed columns in the database, their type and some extra information.
Restrict it with database name, table name, index name pattern, and column name pattern.
Indexes
3

Returns list of indexes and their details.
The first restriction is database name
The second restriction is table name.
The last restriction is the index name pattern.

 

MetaDataCollections
0

Returns this list. Same as using GetSchema() method without parameters.
PrimaryKeys
2

Returns list of columns that participate in primary keys.
The first restriction for this collection is name of a database.
The second restriction is table name.

 

Procedures
2

Returns list of stored procedures on the server.
Restrict it by database name and procedure name.
ReservedWords
0

Lists all reserved words used in the server.
Restrictions
0

Returns list of possible restrictions and their default values for the metadata collections.
Tables
2

GetSchema("Tables") returns list of tables in the current database.
The first restriction for this collection is name of a database. If specified, the method returns all tables within the database.
The second restriction is table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve names of tables that match the mask.

 

UniqueKeys
2

Returns list of columns that participate in unique keys.
The first restriction for this collection is name of a database.
The second restriction is table name.

 

UserPrivileges
1

Lists all users and their privileges on the server.
When restricted by username, returns information about specific user.
Users
1

Lists all users on the server.
When restricted by username, returns information about specific user.
ViewColumns
3

Returns list of columns used by views in the database.
Restrict it with database name, table name and column name.
Views
2

GetSchema("Views") returns list of views in the current database.
The first restriction for this collection is name of a database.
The second restriction is view name mask.

 

 


CollectionName    #    Name
Columns    1    TABLE_CATALOG
Columns    2    TABLE_SCHEMA
Columns    3    TABLE_NAME
Columns    4    COLUMN_NAME
Indexes    1    TABLE_CATALOG
Indexes    2    TABLE_SCHEMA
Indexes    3    INDEX_NAME
Indexes    4    TYPE
Indexes    5    TABLE_NAME
Procedures    1    PROCEDURE_CATALOG
Procedures    2    PROCEDURE_SCHEMA
Procedures    3    PROCEDURE_NAME
Procedures    4    PROCEDURE_TYPE
Tables    1    TABLE_CATALOG
Tables    2    TABLE_SCHEMA
Tables    3    TABLE_NAME
Tables    4    TABLE_TYPE
Views    1    TABLE_CATALOG
Views    2    TABLE_SCHEMA
Views    3    TABLE_NAME

 

集合:

MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Users
Databases
Tables
Columns
StructuredTypeMembers
Views
ViewColumns
ProcedureParameters
Procedures
ForeignKeys
IndexColumns
Indexes
UserDefinedTypes

 

数据类型:

smallint System.Int16 16
int System.Int32 8
real System.Single 13
float System.Double 6
money System.Decimal 9
smallmoney System.Decimal 17
bit System.Boolean 2
tinyint System.SByte 20
bigint System.Int64 0
timestamp System.Byte[] 19
binary System.Byte[] 1
image System.Byte[] 7
text System.String 18
ntext System.String 11
decimal System.Decimal 5
numeric System.Decimal 5
datetime System.DateTime 4
smalldatetime System.DateTime 15
sql_variant System.Object 23
xml System.String 25
varchar System.String 22
char System.String 3
nchar System.String 10
nvarchar System.String 12
varbinary System.Byte[] 21
uniqueidentifier System.Guid 14
posted @ 2015-03-24 12:23  ®Geovin Du Dream Park™  阅读(380)  评论(0编辑  收藏  举报