SQL数据库信息查询

--1.0 获取服务器上所有的数据库(注意:此时的数据库连接字符串需要连接master库,因为所有的库信息都保存在系统库中)
select name as dbName from sysdatabases order by name

--2.0 获取库中所有表(注意:此时的数据库连接字符串是你要操作的库,已经不是master库了)
--2.1 方法1:
select [name] from sysObjects where xtype ='U' and [name]<>'dtproperties' order by [name]
--2.2 方法2:
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' order by TABLE_NAME

--3.0 获取表的字段
--3.1 方法1:获取0行通过获取的
--表的 列(table.Column) 获取列名(ColumnName)和列的类型(Type)Column.ColumnName,Column.DataType
--实例:DataTable table = ExecuteDataTable("select top 0 * from " + tablename);
/*private string[] GetColumnNames(DataTable table,bool IsParameter)
{
string[] columns = new string[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn dataCol = table.Columns[i];
if (IsParameter)
columns[i] = "@" + dataCol.ColumnName;
else
columns[i] = dataCol.ColumnName;
}
return columns;
}
*/
select top 0 * from EmailInfo
--3.2 方法2:
SELECT colorder=C.column_id,ColumnName=C.name,TypeName=T.name, Length=C.max_length, Preci=C.precision,
Scale=C.scale,
IsIdentity=CASE
WHEN C.is_identity=1 THEN
N'1'
ELSE N''
END,
isPK=ISNULL(IDX.PrimaryKey,N''),
Computed=CASE
WHEN C.is_computed=1 THEN
N'1'
ELSE N''
END,
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date,
cisNull=CASE
WHEN C.is_nullable=1 THEN N'1'
ELSE N''
END, defaultVal=ISNULL(D.definition,N''),
deText=ISNULL(PFD.[value],N'')
FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0
INNER JOIN sys.types T ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
LEFT JOIN ( SELECT IDXC.[object_id], IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN
'DESC'
WHEN 0 THEN
'ASC'
ELSE ''
END,
PrimaryKey=CASE
WHEN IDX.is_primary_key=1 THEN
N'1'
ELSE N''
END, IndexName=IDX.Name
FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id
INNER JOIN ( SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id ) IDX
ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id
WHERE O.name=N'EmailInfo' ORDER BY O.name,C.column_id