The database in sql server contains the data in tables. And these tables contain rows and columns. The columns are the field names and the information of that field is stored in rows.
So if we count the number of rows of the entire database tables separately, we will actually count the records of our database.
In this tutorial, we will be counting records in sql database by some Implicit SQL functions.
Step 1- Query Structure
In the Query Editor, select the table name from the system tables called sysobjects, and maximum number of Rows from sysindexes.
Sysobjects basically is a System table which contains all the information about database in sql server whereas sysindexes is a system table which contains the information about the data stored in those tables.
Next, we will insert an inner join on the sysindexes ID and connect it to the ID of sysobjects.
After that’s done, we will filter the tables with a where clause and specify “u” as the xtype. “U” means that you are only interested in the User Defined tables. If you want the data from the system defined tables then you can specify “s” as the xtype.
With that done, we will group the data by table name and order the results in descending order, so that we can have the names of the tables having more data appear first.
Hence the complete query would be:
Select TableName = sysobjects.name,
Rows = max(sysindexes.rows)
From sysobjects
INNER JOIN sysindexes
on sysobjects.id = sysindexes.id
where sysobjects.xtype = ‘u’
group by sysobjects.name
order by Rows DESC
And this was all about counting records in sql Server.
Home