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

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




Second

Sortest way which list out all procedure related to table

select
so.name,
sc.text
from
sysobjects so
inner join syscomments sc on so.id = sc.id
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 : - http://www.codeproject.com/tips/61424/Find-Sp-form-database-which-is-related-to-using-ta.aspx

Linq Deferred Execution

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
example

//Query get executed at this point
foreach( customer c in query) 
{
  // your code 
}

Or

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 : - http://www.codeproject.com/tips/59614/Linq-Deferred-Execution.aspx

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

Following are the different ways to get the number of records in a table:


First
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)

Second
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

Third
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'

Fourth
List rows with the row count.

SELECT PKEY, FIRSTNAME, LASTNAME, ROW_NUMBER() OVER (ORDER BY PKEY) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM app_employee

SQL Group by With Joins

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:

EmployeeID
EmployeeFirstName
EmployeeLastName
EmployeeEmailID
EmployeeContactNo

Structure of Sales:

SalesID
SalesEmployeeID
SalesDate
SalesTotal

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