Tag Archives: sys.schemas

Get row count for tables quickly in SQL Server

As a DBA, This is my prime responsibility to keep track of table size and number of rows it has. If I notice any unusual increase or decrease in size or number of rows in table, I must have to investigate this.

I have seen people used to execute COUNT() aggregate function on table to know the total number of rows in table. This operation may affect performance of server if table is having billions of row.

I don’t recommend COUNT() aggregate function on table to know total number of rows in table. I recommend to use sys.partition and sys.allocation_units catalog view along with sys.tables and sys.schemas catalog view.

I have created one user defined table valued function which do this job for me. It is much faster then COUNT() aggregate for sure.

CREATE FUNCTION GetRowCount()
RETURNS TABLE
RETURN
SELECT
sch.name AS SchemaName,
SysTab.Name AS TableName,
sch.name + '.' + SysTab.name AS QualifiedName,
SUM(
CASE
WHEN (Parti.index_id < 2) AND (alloUni.type = 1) THEN Parti.rows
ELSE 0
END
) AS Rows
FROM sys.partitions AS Parti
INNER JOIN sys.allocation_units AS alloUni ON Parti.partition_id = alloUni.container_id
INNER JOIN sys.tables AS SysTab ON SysTab.object_id = Parti.Object_ID
INNER JOIN sys.schemas AS sch ON sch.schema_id = SysTab.schema_id
GROUP BY SysTab.name, sch.name
GO

Once you create “GetRowCount()” table valued function in your database, you can query it like this:

SELECT * FROM GetRowCount()
--you can even filter this function
--to get value for specific table for eg:
--WHERE TableName='SalesOrderDetail'
ORDER BY Rows DESC
GO

This is one of fastest approach to know the total number of rows in table. I have already written two articles related to table size, if you are interested, have a look:

Calculate Table & Schema size in SQL Server (Click Here)
List of Table in Filegroup with table size in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Search objects like Stored Procedure, View, Table in SQL Server

It happens many time that we don’t know the exact name of stored procedure or table or view etc. and we have to either look at object explorer and scroll all the way down for object we are looking for or we can use “Filter” facility of SSMS but I prefer one small function I have written very long back because I am a script buddy and try to avoid mouse as long as possible.

Sometime, we may want to find out whether function, table or view we are looking for, is used in any other SP or View to check dependency, I used the same function. BTW, you can use “SP_Depends” or “Information_Schema.routines” to find dependency of the object which is more accurate way for find dependency.

Here is the table valued user defined function which I have developed a long back and still using it. In fact, I am so used to it now.


CREATE FUNCTION ObjectSearch

(

@SearchString VARCHAR(100)

)

RETURNS TABLE

RETURN

SELECT

DISTINCT scm.name AS SchemaName,

obj.name AS ObjectName,

obj.type_desc AS ObjectType

FROM sys.objects obj

INNER JOIN syscomments sysCom ON sysCom.Id = obj.object_id

INNER JOIN sys.schemas scm ON scm.schema_id = obj.schema_id

WHERE

is_ms_shipped = 0 AND sysCom.text like '%' + @SearchString + '%'

GO

Okay, not we have “ObjectSearch” function in our database. I have created this function in my AdventureWorks2012 database and now I am going to find where “Department” table is used so I would execute following simple SELECT statement with “ObjectSearch” function.


SELECT * FROM ObjectSearch('Department')

GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find stored procedure in your database with T-SQL in SQL Server 2005/2008

I have observed quite a few times in different forum, people keep asking question that I have created stored procedure in my database but I can’t find it. Well there are quite a few reason for that, either you have created your stored procedure in different database and you are trying to run in different database or you have not specified schema name for your stored procedure so default schema of your login will be automatically applied to it, while running SP, you don’t specify that schema name and you will face an error like:

“Could not find stored procedure ‘Your SP name’”

Well, to ensure that you stored procedure is created and it is exists in particular database, I used to run following very simple query to confirm.

select ss.name + ‘.’ + sp.name as ‘Available Stored Procedure’ from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id

Above query will show you all the available stored procedure along with its schema name available in your database.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find fully qualified path in SQL Server 2008/2005

It has been observed so many times that when programmer creates table or stored procedure in SQL Server and when tries to run it with its name, they face an error that object doesn’t exists. If you are sure that you have created object even though it is not available even after you are having full permission in database than there is a case that, object owns by particular schema and you are not referring it while calling.

There are few schema in Adventureworks database and each owns few tables. If you try to use table name only in SELECT statement, you will face an error given below.

–try running below query

use AdventureWorks

select * from Address

–it will show you below given error

–Msg 208, Level 16, State 1, Line 1

–Invalid object name ‘Address’.

–reason is Address table is owned by Person schema

–so if you try like below, you are done.

select * from Person.Address

–Person.Address

–we have specified schema name and table name in above query

–but the good practise is to specify ServerName.DatabaseName.Schemaname.ObjectName

–this is called fully qualified path and to find out fully qualified path of object

–use below give T-SQL statement.

select ‘[‘+@@SERVERNAME+‘]’ + ‘.’ +DB_NAME() + ‘.’ +ss.name+‘.’+so.name as ‘Full Qualified Name’ from sys.objects so join sys.schemas ss

on so.schema_id=ss.schema_id where so.name=‘Address’

Enjoy!!!!