Useful Commands in SQL

Question: Can an INSERT query return a value?

Answer: Yes

SQL Query: 

insert into DS_UserTypes (UserTypeName,[Description])
output inserted.UserTypeid,inserted.UserTypeName values
('TeamLead','Nothing')

C# Code:

string Query = "insert into DS_UserTypes (UserTypeName,[Description]) "
+ "output inserted.UserTypeid,inserted.UserTypeName values "
+ "('TeamLead','Nothing')";
con.Open();
SqlCommand cmd = new SqlCommand(Query, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
   Console.WriteLine("User type Id :" + rdr[0].ToString());
   Console.WriteLine("User type name :" + rdr[1].ToString());

}

Question: How to determine the Last Row identity?

Answer:

select distinct @@IDENTITY 'Last Row Id' from DS_UserTypes
select * from DS_UserTypes

Output:



Question: How to get the latest rows against an attribute in a table?

Answer:

with budget as (select RANK()over(partition by uniquematterid,assignmentid
order by BudgetApprovedDate desc) as [Rank],
* from AS_Budget )

select [RANK],Budget_ID 'Id',MatterId 'TaskId',LawfirmID 'VendorId'
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'
from budget where MatterId=2

Query output:



From this we can see that the latest approved work id has the rank 1.

Now we can re-write our query as:

select [RANK],Budget_ID 'WorkId',MatterId 'TaskId',LawfirmID 'VendorId'
,LeadLawyerId 'PersonInCharge',BudgetApprovedDate 'ApprovedDate'
from budget where [Rank]=1

Then it will return only the latest row of data against each attribute in the partition by clause.

Question: Update the table with a Join query.

Answer:

Before updating:

select * from DS_User

After updating:

update a set a.active='False' from DS_User a inner join DS_UserTypes b on a.UserType=b.UserTypeid
where b.UserTypeid=5

select * from DS_User

Question: How to determine whether a table exists in the database?

Answer:

Sp_tables ‘%table_name%’

For example:

Sp_tables '%ds_%'

Question: How to determine a procedure using a Query?

Answer:

sp_helptext ‘Procedure_name’

For example:

Sp_ helptext 'GetImageID'

Question: How determine is specific table is used in any of the stored procedure?

Answer:

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN
sysobjects so on sc.id=so.id WHERE sc.text LIKE '%table_name%'

For example: 

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN
sysobjects so on sc.id=so.id WHERE sc.text LIKE '%users%'