How to find all objects in a SQL Server Schema

Today I’m migrating some databases from one server to another. Some of these applications are quite old, and user accounts that were created for them belong to people who have long since left the project. As I delete the users, SQL Management Studio asks me if I want to delete the associated schema. My thought process is “Yes… ? Wait, what will that delete, exactly?” Unfortunately, it doesn’t tell you what that schema has in it. Fortunately, there’s a simple query you can run that will show you:

Run the above query in the database you’re working in (not master). Replace ‘dbo’ in the query above with the schema you’re interested in. In my case, it was the schema matching the SQL Server database user I was about to delete (you probably don’t want to delete the user or schema ‘dbo’, by the way).

Incidentally, if you’re moving databases with existing users and setting up new logins for them, you’ll probably run into the “User, group, or role ‘thing’ already exists in the current database” error at some point. I wrote a quick article on how to fix this, too.

  • Sameer Sayani

    My below query will give list of all tables in a db with Schema and RowCount:-

    SELECT
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
    FROM
    sys.tables t
    INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    GROUP BY
    t.NAME, s.Name, p.Rows
    ORDER BY
    s.Name, t.Name