How do I programmatically access data about my database?

I'd like to access the list of tables and for each table report number of ro开发者_Go百科ws, disk space used, etc. It would be nice to get these details at the database level, too.

How do I do this programmatically?

---

Jason's answer above is good, but more generally. you're looking at Information Schema. Also Wikipedia's entry:

http://en.wikipedia.org/wiki/Information_schema

---

You can just open a connection and query the database:

using(var connection = new SqlConnection(connectionString)) {
    connection.Open();
    using(var command = connection.CreateCommand()) {
        command.CommandText = "SELECT * FROM SYS.TABLES";
        using(var reader = command.ExecuteReader()) {
            while(reader.Read()) {
                Console.WriteLine(reader["name"]);
            }
        }
    }
}

You can Google for the query strings for the other information that you want.

---

Create a SqlConnection to your database and open the connection.

SqlConnection conn = new SqlConnection("Data Source=Servername;Initial Catalog=Marketing;Integrated Security=SSPI");
conn.Open();

Create a SqlCommand and assign the CommandText to the value of SQL you require.

SqlCommand cmd = new SqlCommand("PLACE SQL HERE", conn);

Tables and number of rows:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

Space used:

EXEC sp_spaceused 'tablename'

---

This script doesn't include schema names but gets you most of the information you want for the current database. I'm sure you can adapt it into a stored procedure.

SET NOCOUNT ON
GO
DECLARE @tblSpaceUsed TABLE
(
    [name] sysname NOT NULL,
    [rows] int NOT NULL,
    [reserved] nvarchar(50) NOT NULL,
    [reservedKB] int NULL,
    [data] nvarchar(50) NOT NULL,
    [dataKB] int NULL,
    [index] nvarchar(50) NOT NULL,
    [indexKB] int NULL,
    [unused] nvarchar(50) NOT NULL,
    [unusedKB] int NULL
)

DECLARE @tableName sysname
DECLARE @tableNames CURSOR

SET @tableNames = CURSOR
FAST_FORWARD
FOR
SELECT DISTINCT
    ss.name + '.' + st.name
FROM 
    sys.tables st
    INNER JOIN
    sys.schemas ss
        ON st.schema_id = ss.schema_id

OPEN @tableNames 
FETCH NEXT FROM @tableNames INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @tblSpaceUsed ([name], [rows], [reserved], [data], [index], [unused]) EXEC sp_spaceused @tableName

    FETCH NEXT FROM @tableNames INTO @tableName
END

CLOSE @tableNames

UPDATE 
    @tblSpaceUsed
SET
    [reservedKB] = CONVERT(int, LEFT([reserved], LEN([reserved]) - 3)),
    [dataKB] = CONVERT(int, LEFT([data], LEN([data]) - 3)),
    [indexKB] = CONVERT(int, LEFT([index], LEN([index]) - 3)),
    [unusedKB] = CONVERT(int, LEFT([unused], LEN([unused]) - 3))

SELECT
    *
FROM
    @tblSpaceUsed