Query to get all tables info in a Database

1

Can anyone help me with this
i need a query to get the details of all the tables in a database like table name, size, indexes, type (primary key & unique), row count, avg % fragmented etc.

Thank you!!

2

That's quite a few different things you are asking. Here are 2 queries:

  1. list DBs includes some info

    select * from sys.tables

  2. Get the space used for all tables, includes row count

    set nocount on
    create table #spaceused (
    name nvarchar(120),
    rows char(11),
    reserved varchar(18),
    data varchar(18),
    index_size varchar(18),
    unused varchar(18)
    )

    declare Tables cursor for
    select name from sysobjects where type in ('U')

    OPEN Tables
    DECLARE @table varchar(128)

    FETCH NEXT FROM Tables INTO @table

    WHILE @@FETCH_STATUS = 0
    BEGIN
    insert into #spaceused exec sp_spaceused @table
    FETCH NEXT FROM Tables INTO @table
    END

    deallocate Tables

    select * from #spaceused order by cast(left(Reserved, len(Reserved) - 2) as integer) desc
    drop table #spaceused

3

It sounds like you've been asked a trick question on an interview. Lookup sys.dm_db_index_physical_stats, OBJECT_NAME, OBJECT_SCHEMA_NAME, INDEXPROPERTY, know that #of pages/128.0 = Megbytes, and what's in the sys.Indexes table and, if you really want to step out on a limb, what's in the old sys.sysindexes table so that you can also find out how many rows have been added/changed since the last stats update. And yes... although deprecated, the sys.sysindexes table is still in SQL Server even through 2016 and Microsoft still uses it to determine if stats need to be updated.

Thanks to the sys.dm_db_index_physical_stats function, there's no need for a cursor or loop to step through all the tables and indexes in a database. If you hold your mouth just right, you can also tell if a table or index is partitioned.