Get all database from SQL Server 2005
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace=" System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
string connectionString = "Data Source=.;Initial Catalog=master;Persist Security Info=True;User ID=sa;Password=sa";
protected void Page_Load(object sender, EventArgs e)
{
List<string> DatabasesNameList = GetDatabasesName();
foreach (string DatabaseName in DatabasesNameList)
{
Response.Write("Database Name: ");
Response.Write(DatabaseName); Response.Write("<br />");
Response.Write("----------------------------------------------------------------------------------------------------------"); Response.Write("<br />");
Response.Write("----------------------------------------------------------------------------------------------------------");
Response.Write("<br />");
List<string> TablesNameslist = GetTablesName(DatabaseName);
foreach (string TableName in TablesNameslist)
{
Response.Write("Table Name: ");
Response.Write(TableName);
Response.Write("<br />");
DisplayTables(TableName, DatabaseName);
Response.Write("<br />"); Response.Write("<br />"); Response.Write("<br />");
}
Response.Write("<br />"); Response.Write("<br />"); Response.Write("<br />");
}
}
void DisplayTables(string tablename, string databasename)
{
string connectionString = GetDatabaseConnectionString(databasename);
string Query = "SELECT * FROM [" + tablename + "]";
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter sda = new SqlDataAdapter(Query, conn);
conn.Open();
DataSet ds = new DataSet();
sda.Fill(ds, tablename);
DataTable tblSchema = ds.Tables[tablename];
conn.Close();
Response.Write("Column Name ------ Data Type ------ Unique ------ Auto Increment ------ Allow DBNull");
Response.Write("<br />");
foreach (DataColumn dc in tblSchema.Columns)
{
string DatabaseInfo = dc.ColumnName + "------" + dc.DataType + "------" + dc.Unique + "------" + dc.AutoIncrement + "------" + dc.AllowDBNull;
Response.Write(DatabaseInfo);
Response.Write("<br />");
}
}
string GetDatabaseConnectionString(string DatabaseName)
{
string Res = "Data Source=.;Initial Catalog = DatabaseName ;Persist Security Info=True;User ID=sa;Password=sa";
Res = Res.Replace("DatabaseName", DatabaseName);
return Res;
}
List<string> GetTablesName(string DatabaseName)
{
string connectionString = GetDatabaseConnectionString(DatabaseName);
DataTable tables = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";
connection.Open();
tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
List<string> list = new List<string>();
foreach (DataRow row in tables.Rows)
{
list.Add(row[0].ToString());
}
return list;
}
List<string> GetDatabasesName()
{
DataTable tables = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "select name from master..sysdatabases";
connection.Open();
tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
}
List<string> list = new List<string>();
foreach (DataRow row in tables.Rows)
{
list.Add(row[0].ToString());
}
// We can not visit the system databases
list.Remove("msdb");
list.Remove("master");
list.Remove("tempdb");
list.Remove("model");
list.Remove("ReportServer");
list.Remove("ReportServerTempDB");
list.Remove("aspnetdb");
return list;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Visit Database</title>
</head>
<body>
<form id="form1" runat="server">
</form>
</body>
</html>
