SQL Server Query to get the row count for all tables in a DB.

Below is the query that gives the result set of row count in each table in a DB.

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Result set:

Identity Column in SQL Server

The below post will explain about identity column in SQL Server. It is very basic thing only but when i was a fresher i struck a lot to understand. Reason is i did not a find any good post regarding this concept.

Identity is nothing but defining the seed and increment value to the column. In below case i have defined the seed value as 1000 and increment value should be 1.

Below is the script to define identity while creating table.

 

In this case first row of avb column should be 1000 and second is 1001 and third is 1002 like that it will get increment by 1.

In GUI mode we can define identity as below picture.

1.Right click the table and click design and at bottom you can see the identity properties.

 

 Define Identity with Data:-

We can't define identity for the table which has data. 

Then how to define identity for the table which has data:-

We have option . If you want to define identity for the table A (which has data).

1.Then create a same structure of table with Identity as A1 .

2.Insert data from your table A to A1.

3.If you receive an error like cannot insert into identity column the run the below query.

set identity_insert table name off.

4. Now you can insert data from A to A1.

5. confirm both the table has same data then drop the table A and rename the table A1 to A.

6. Again run the below query to on the identity insert.

set identity_insert table nameON.

Note- We can define only one identity column for a table.

Thats it. Its very easy.......