tablename

Proper Aliasing will help you somewhere TIP#104

Recently, When we were delivering session on “SQL SERVER” one of the persons asked why we require Aliasing so I thought this might be question in everyone’s mind. So lets start with couple of well know statements then we will see the actual problem where we need it explicitly. There are two type of aliasing …

How easy to determine table dependencies ? TIP # 86

  Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides. By using this stored procedure we can easily determine all the dependencies of particular table. The stored procedure is sp_msdependencies We can use this stored procedure as shown below Execute sp_msdependencies …

How to copy table structure only from a SQL Query ? Tip #69

  Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?” So, Below is simplest query to copy structure only of a table into another table. SELECT * Into #tmpStudentStructureFROM tblStudentSource WHERE 1= 0 in the above query we want to …

What is TVP (Table Value Parameter) & How to use it ? TIP #57

  This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter. I know above statement is not digestive enough so lets understand this by an example. Suppose , I have a table tblStudent with …

Maintenance of fragmented Heap table–TIP #55

  Friends, In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so. Now our task is to run maintenance of this highly fragmented table. So you have to run following command “ALTER TABLE  TABLENAME REBUILD; For example  I run the Rebuild …

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

  Heap table:- A table without cluster index is called Heap table. Now you are thinking why we are talking about this. So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table. Now our next question is how to determine …

A hidden feature sp_MSforeachtable–run on entire tables of a Database TIP# 51

Problem:- Sometimes it happened that you need to run a single statement on entire tables which exists in database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name. Solution: Although the solution we are thinking is correct there is …

How to update statistics ? TIP #14

b In last TIP tip#13, We learn how to find last updated statistics status. Now we know when it last updated so it may be require we need to update statistics for some of table. So to update statistics we need to write following command ( if we want to update  statistics of entire tables …

How to determine table reference /used in stored procedure- TIP #9

To determine table used in stored procedure or table reference We need to run following commandGoSELECT object_Name(Id)as objectName,text FROM syscomments  WHERE text like ‘%tblUser%’GoWe can use following command as wellGoSELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like ‘%tblUser%’ GO In the above queries we have used tblUser as table name we can replace it and add any …