Thursday, February 25, 2010

Find Sp form database which is related to(using) table XXX

Shout it kick it on
Problem : - During project devlopment I got the requirement that I have to modified the column name of the table which is used in the no of procdure i.e i have to found all proceudre relatd to that table and modifiy it

For the above problem follwing are the solution that i used

First :
I follow the following step to get list of stored procedure in sql server 2005 management studio

Right click on Table name >> View dependencies

Which list all the procedure and table related to it


Sortest way which list out all procedure related to table

select, sc.text from sysobjects so inner join syscomments sc on = where sc.text like '%ROLES%'-- name of the table and sc.text like '%select%'--found procedure where select * from table name used

Advantage of this :
By the above query i can list out only those stored procedure which is contain select * table name

if i have to list stored procedure which contain update table name than i just have to change my filter condition

The above query is ver usefull when your table field name get change you have to modify the all stored proceudre which is using it

Rate it : -

Linq Deferred Execution

Shout it kick it on
To understand how execution take place consider below code:

//Query not going to execute at this point var query = from customer in db.Customers where customer.City == "Paris" select customer;

Most of the people think that the query gets executed at this place but the its not right its get executed when I use query collection for

//Query get executed at this point foreach( customer c in query) { // your code }
int count = (from customer in db.Customers where customer.City == "Paris" select customer).Count();
or to .ToList() or any other function which cause execution.

Consider another scenario

I write code like this

var query = from customer in db.Customers where customer.City == "Paris" select customer;

than change code to below line

query = from customer in db.Customers where customer.City == "Mumbai" select customer;

and than try to use query object

//it get count of all customer which is related to city Mumbai rather than paris. int count = query.Count();

Rate it on : -

Number of different way to get total no of row from tables

Shout it kick it on
Following are the different ways to get the number of records in a table:

List all table of the database with row counts.

SELECT Table_Name = object_name(object_id), Total_Rows = SUM(st.row_count) FROM sys.dm_db_partition_stats st GROUP BY object_name(object_id) HAVING SUM(st.row_count) <> 0 ORDER BY object_name(object_id)

List table with the no of row in table.

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] WHERE sysobjects.xtype = 'U' and sysobjects.[name]='App3_Employee' GROUP BY sysobjects.[name] ORDER BY max(rows) DESC GO

List table with row count.

SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'app3_employee'

List rows with the row count.


SQL Group by With Joins

Shout it kick it on
How to use Group By clause when joining to table

Let's consider one scenario where I have two table employees (contains employee detail) and sales (contains infomation about sales done by employee).

Structure of Employee:

Structure of Sales:

Now I want to get total sales done by employee with employee name. For that, I write a query like:

Select EmployeeFirstName,EmployeeLastName,sum(SalesTotal)
from Employee 
inner join Sales 
on EmployeeID= SalesEmployeeIDgroup 
by EmployeeFirstName,EmployeeLastName,SalesTotal

But there is one problem in the above Query. I have to add two more extra fields in group by clause which make query inefficient and make no sense logically So the solution for this is to use derive table which makes sense logically and clears query

Select EmployeeID,EmployeeFirstName,EmployeeLastName,TotalSalesfrom 
Employee inner join 
     (Select SalesEmployeeID,sum(SalesTotal) as TotalSales 
                        from Sales group by SalesEmployeeID) empSales 
on empSales.SalesEmployeeID= EmployeeID