Tag Archives: Ritesh Shah

Do I need backup of my database?

If you are working as a DBA or you have responsibility to maintain your SQL Server database up & running, take my words, you have NO OPTION of full database backup.
Recently I came to know that one person is not taking backup just because he has RAID and Mirroring setup so he is least worried about backup. Believe me, no matter what redundant hardware / software and high availability setup you have, you MUST take backup regularly because even good and up-to-date disaster recovery system could get failed. So, you never know, when and how do you need your latest backup file.
Let us discuss why database backup is really that much important.
Suppose you have high availability solutions like RAID or server clustering but what if you hardware gets corrupt and may be complete disk array get corrupt? You have to build up your database from backup only.
Sometime, catastrophic events or natural disasters like flooding, earth quake etc. could damage your hardware or may be complete data center and if you have no backup at other location, you have no place to go for.
May be sometime Security issues comes to the picture and somebody intentionally or unintentionally damage you data which could be affected adversely and in that situation you might look for the latest database backup to restore that data.
Sometime your OS or even your SQL Server gets corrupted and you might need to restore data from backup itself.
In short, I would HIGHLY recommend database backup as “Prevention is always better than cure”. So, keep strong backup policies with full database backup, differential database backup and transaction log backup, depends on your need and business policies.
BTW, I am not against the high availability concepts like mirroring, clustering, replication or log shipping. You should implement those too as per your business needs but also MUST keep strong backup policies.
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Index Fill Factor in SQL Server

Today once again I felt to write something about Index, so open up the list of Index articles I have written, find out the topic which are still missing for concept of Index and thought to write about fill factor.
Generally, when you create an Index, your data get stored in B-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAF Page”. “Leaf Page” would have your actual data sorted in order of Index key and each “Leaf Page” would be of 8KB.
When you insert/update/delete data to/from table, it tries to insert in the proper data page according to the record you are inserting. For example, you have Index on SSN number you are inserting new row with SSN number, SQL Server tries to insert that record in the appropriate page, suppose your SSN starts with “2” so it will find what is the last page which has SSN number starts with “2”, SQL Server will insert your new row in that page only. If your 8 KB page is full and don’t have room to accommodate new row whose SSN starts with “2”, it will split page and the data in that page will be shared between two pages so now you have two pages which are half full so your row will be accommodated in that page.
If your page would already had space for accommodating new row, you wouldn’t need to wait for extra I/O overhead and wait until page split gets finish and space for your row would become available.
This is the time when FillFactor comes into the picture. Fill Factor decides how much your page would be filled up initially. Suppose you give 10 in FillFactor than your data page will consume only 10% of your 8KB page size and when you exceed this limit of 10%, it keeps 90% page empty and create new page for other records.  Now, when you insert new records, you don’t need to worry about I/O overhead of page split as you would have 90% free space and your record will be accommodate in that space easily. So, if you have lower number of Fillfactor, you can decrease I/O over head generated by Page Split which helps you to write your data faster.
Now, you might be thinking now that why shouldn’t I use low number always like 5% or 10% in Fillfactor? Well, it will decrease your page split but it will increase number of data page in your index so every time you scan your index, you have to read more pages and it is again over head while reading the data and decrease the speed of reading data. Suppose you have 10 records in one table and its size is 8KB, you can fit all 10 records in one page only but if you have fill factor of 50 than those 10 records will be stored in two pages rather than one while creating an index.
Uff, what to do now? if we decrease fillfactor figure, it helps in writing but creates problem in reading and if we increase fillfactor figure may be 100% than helps in reading but creates issues while writing in database?
You have to be reasonable and have to take decision based the load on your database, you have to decide first that you have more writes or more read?
Personally I majority go for figure somewhere between 80 to 90 for fillfactor so that you have some space remain for new records and decrease page split at the same time, we don’t keep too much free space in data pages so number of pages doesn’t get increased. Even, it is not final figure I use for all my databases, if my database is read only or very rare insert/update, I would go even for 100% Fillfactor and if my database is write intensive, I would have even 50% Fillfactor.
BTW, you can define Fill Factor while creating an Indexor rebuilding an Index or you can set default value for fill factor via following T-SQL (by default it is 0 in SQL Server).
–turning on advanced configuration option
Sp_configure ‘show advanced options’,1
GO
RECONFIGURE
GO
–setting up fill factor
sp_configure ‘fill factor’, 90
GO
RECONFIGURE
GO
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

Before we jump into the core logic, let me explain you what are the use of this functions / property.
BTW, this is the table to be used in example:
CREATE TABLE EMPTEST
(
FirstName varchar(10)
,MiddleName varchar(10)
,LastName varchar(10)
)
INSERT INTO EMPTEST
select ‘Ritesh’,‘A’,‘Shah’ UNION ALL
select ‘Roger’,NULL,‘Federer’ UNION ALL
select ‘Steffi’,NULL,‘Graf’
GO
Select FirstName,LastName fromEMPTEST
ISNULL: This function replaces the NULL value with specified value given in function. Suppose we have probability to have Middle Name NULL in Employee table, we could do something like below given TSQL statement.
Select FirstName, ISNULL(Lastname,) as LastName From EMPTEST
If Lastname will be NULL, it will be replaced with blank in result set.
COALESCE: Basically COALESCE function will return first not null value from the given list, so we can use this function in place of ISNULL too, like this:
Select FirstName, COALESCE(Lastname,) as LastName From EMPTEST
NULL is nothing but the absent of value, it doesn’t even represent a blank or space or zero. When you try to concatenate two or more than two strings and any of the string is NULL, it will return the NULL only. See following example:
 
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
You will get first record “Ritesh A Shah” as full name and remaining two records as NULL as those two records are having NULL value in its MiddleName field so concatenate results will be NULL only.
You can have solution for that with ISNULL & COALESCE.
Select FirstName + ‘ ‘ + ISNULL(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
Select FirstName + ‘ ‘ + COALESCE(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
In this solution, you have to wrap up all the fields all the fields with either ISNULL or with COALESCE if it has probability of containing NULL value. If you have many fields in one query which needs this wrapping, it is bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULL property comes as a rescue.
CONCAT_NULL_YIELDS_NULL: this property controls concatenation of the string, like what should do if any of the string is NULL and it is being used in concatenation.
You can set this property at database level by ALTER DATABASE command or you can use this for your current session/connection.
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
As I have already told you that above query returns two record with NULL value as FullName, you can do something like below TSQL to set CONCAT_NULL_YIELDS_NULL  for your batch.
SET CONCAT_NULL_YIELDS_NULL OFF;
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
SET CONCAT_NULL_YIELDS_NULL ON;
By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but you can turn it OFF for your batch or for your database (by ALTER DATABASE command). It will save you from writing ISNULL and COALESCE command for many times in your query.
If you want to check whether your database is set to TRUE or FALSE for this property or your SESSION has this property TRUE or FALSE, you can use following TSQL.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsNullConcat’)
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
Personally I prefer to go for either ISNULL or COALESCE rather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.
–> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 along with previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ON in new versions and you will not be able to set it OFF
 –> If you have this option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.
Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.
 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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Should Auto_Close database property be ON or OFF in SQL Server?

One of my friends just leased one dedicated SQL Server hosting server with managed support for his own business purpose. Though it is hosted at hosting company’s data centre with very good hardware configuration and being used for his own (one) database only, it was performing very slowly at certain point of time though there is no heavy work load.
He asked me if I can help him out in this matter so I had quick look into it and after sometime I found Auto_Close property a culprit. 
Let me first tell you what this property is and how & where does it comes into the picture.
When we first time access or open the database in SQL Server, our database is assigned some resources to maintain its own stat, some memory for its use, some buffer space and many more. Auto_Close helps you to free up these resources when last user disconnects from the database.
This is good thing to keep unused resource free but what if your last user disconnects and every resource gets free for claim and after few minutes of freeing up resource another user came to connect to this database? Obviously SQL Server will assign all resource to the database again and user has to wait until all resource assigned to the database means, slow performance for that user first time.
If you are having only one or may be few databases in your instance, I highly recommend keep Auto_Close property to OFF. I have seen that hosting company keep these settings on for their shared hosting server where you can find hundreds of databases in one instance.
BTW, As per Microsoft, this feature will be removed from later version so this is one more reason not to use this setting in your database.
Here is the TSQL to check whether Auto_Close is ON or OFF for your database.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsAutoCLose’);
Here is the syntax which helps you to set ON or OFF for Auto_Close property in your database.
Alter Database YourDatabaseName SetAuto_Close ON
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC (Change Data Capture), one of the reader had tried to do it in his development server but in his server, CDC was already enabled and another co-incident was that, the table he has choose for CDC, was already having its capture instance, may be any of the other team member might have done it and he doesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36
Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was using was already having capture instance so obviously he should use another table for this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing @Capture_Instance name explicitly (not recommended). I will provide TSQL needed from disable CDClater in this article.
Well, these are some of the solution when you face above given error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I know even before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL queries like below when you want to know it.
–list out the name of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out all tables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know in details like which table is CDC enabled
–which is the capture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDC from your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDC from your database
EXEC sys.sp_cdc_disable_db
GO
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required to audit the data, means, insert / update / delete should be tracked on some important table. There are many ways you can capture these information (Audit Trail), I have written quite a few articles on this subject too but all those needs additional code to be written. Fortunately my client is using SQL Server 2008 R2 version so I don’t even need to write down any specific code to capture changed data as there is facility of CDC (Change Data capture) in SQL Server 2008.
Even before we look at CDC in details, make sure you have your SQL Server Agent is running as CDC in SQL Server 2008 will use SQL Server Agent to make audit trail for you. If your SQL Server Agent is not running at the moment, start it from control Panel->Administrative Tools-> Services. You can find “SQL Server Agent (YourInstanceName)”, just start this service and then you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC in SQLHub database
–this will create “cdc” schema in SQLhub database too
–along with “cdc” schema, it will create some system table
–in “cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDC for our table created above.
–when you will enable cdc for ChangeDataCapture table
–it will create two job under SQL Server Agent
–which will read data from transaction whenever you will make any change in data
–and stores it in CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you have orginal table, named “ChangeDataCapture”
–another table to keep all changes “ChangeDataCapture”,
–which has been created in step above
select * fromChangeDataCapture
–following table will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let us make some DML operation in ChangeDataCapture table and
–observe how does it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 represent INSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of UPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 represent value before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of DELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 represent DELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find your backup history in SQL Server

While managing multiple databases, it is often needed to check database backup history like when did we taken backup? Where did we take last few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, few databases backup taken by third party software and all works on schedule on particular time even I keep one small T-SQL script handy to check all information I have mentioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_Name AS BackupPath,
    MedSet.Software_Name AS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_Name AS ServerName,
    bkSet.Database_Name As DatabaseName,
    CASE bkSet.Type     
            WHEN ‘L’ THEN ‘TransactionLogBackup’
            WHEN ‘D’ THEN ‘FullBackup’
            WHEN ‘F’ THEN ‘FileBackup’
            WHEN ‘I’ THEN ‘DifferentialBackup’
        WHEN ‘G’ THEN ‘DifferentialFileBackup’
        WHEN ‘P’ THEN ‘PartialBackup’
        WHEN ‘Q’ THEN ‘DifferentialPartialBackup’
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      –keep your database name in condition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you want to find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
ORDER BY   
      bkSet.Backup_Finish_Date DESC

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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

FileStream in SQL Server 2008+

One of my friends is working on one project which used to store images and some document files. He is using BLOB fields to get this task done, when he was discussing this with me, I suggested him to use FileStream feature instead of the way he is using currently. There are some reasons for that and I am going to explore it in this article.
In SQL Server 2005, we had two option to deal with binary data (image, document etc.).
1.)    Store images/documents in OS’ file system and keep pointer (path) of that images/documents into database table
2.)    Use BLOB fields to store images/documents directly in database table.
So far we used to take any of the above approaches but both have their own limitations. In first approach, transactional consistency is the issue, when you take backup of database, it won’t get backup of the folders where we are keeping our files (images/documents). In second approach, you can store binary data directly in SQL Server database table so whenever you get database backup, it will backed up by its own but it affects performance while converting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.
To overcome these limitations, Microsoft developer team provided very cool feature, named “Filestream” in SQL Server 2008. With help of “Filestream”, you can store images/documents/videos directly in windows NTFS file system, it has no limitations of 2GB like BLOB and when you take backup of database, your Filestream data will be backed up by its own. Apart from that, you can get advantage of NTFS streaming APIs for efficient and performance driven file operation.
To create Filestream enable column in your table, you have to have a file group in your database which is enable for “FileStream” and you will also need one column in your table which should have varbinary(max) data type so that images/documents/videos could be stored there.
Even before doing any of the above things, you have to enable “Filestream” in your SQL Server which is by default disable.
USE MASTER
GO
–if file stream is not already enable in your server
–look at following link to enable it.
–http://technet.microsoft.com/en-us/library/cc645923.aspx
EXEC sp_configure filestream_access_level, 2
–why I have used 2? know it from below link.
—-http://technet.microsoft.com/en-us/library/cc645956.aspx
RECONFIGURE
GO
CREATE DATABASE SQLHubFileStream ONPRIMARY
(
      NAME = SQLHubFileStream_data,
      FILENAME = N’D:\TestDB\SQLHubFileStream_data.mdf’
),
FILEGROUP SQLHubFileStream_FS CONTAINS FILESTREAM
(
      NAME =SQLHubFileStream_FILESTREAM,
      FILENAME = N’D:\TestDB\SQLHubFileStream_FS’
)
 LOG ON
(
      NAME = SQLHubFileStream_LOG,
      FILENAME = N’D:\TestDB\SQLHubFileStream_log.ldf’
);
GO
USESQLHubFileStream
GO
CREATE TABLE Customers
(
      ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
      Name varchar(25),
      CustPhoto VARBINARY(MAX) FILESTREAM
)
GO
–I have already kept “Ritesh-Teerth.JPG” file in my D drive,
–you have to provide your own path and file name here.
INSERT INTO Customers
VALUES
(NEWID(),‘Ritesh Shah’,(select * FROM OPENROWSET(BULK ‘D:\Ritesh-Teerth.JPG’, SINGLE_BLOB) AS img))
GO
select * from Customers
go
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Know your processor by XP_Instance_RegRead SP in SQL Server 2005/2008

XP_Instance_RegRead could be quite a useful extended stored procedure which ships with SQL Server 2005/2008.
Sometime it happens that you don’t know which processor your server is using and you want to know it; however, you don’t have physical access of the server than you can use following T-SQL to know those information.
EXEC master.dbo.xp_instance_regread
‘HKEY_LOCAL_MACHINE’,
‘HARDWARE\DESCRIPTION\System\CentralProcessor\0’,
‘ProcessorNameString’;
BTW, I have already written two articles before too with use of this kind of extended stored procedure.
Please note that if the user you are using to access SQL Server, wouldn’t have permission in OS to read registry, you will face an error rather than result.
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SP_RefreshSqlModule in SQL Server 2005/2008

According to the MSDN, SP_RefreshSQLModule:
Updates the metadata for the specified non schema-bound stored procedure, user-defined function, or view. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.
Rather than going into detailed theory session, let me give you one example to make it clear in your mind.
Suppose you have one table, named “baseTable” and you create one SP, ”usp_getBaseTableData” , which used to return all records of “baseTable” table.  ”usp_getBaseTableData” is now depend on ”BaseTable”. SQL Server used to store this relational information in “sysdepends”.  In “sysdepends”, information gets stored with Object_ID rather than Object_Name so if you drop your table “baseTable” and check the dependency of ”usp_getBaseTableData”, you won’t get anything but the message like this:
Object does not reference any object, and no objects reference it.
Obviously you will not get any reference as you have already dropped the table, now create the table with same name and structure and after that run SP_Dependsto check dependency and you will again get the same message, though you have created table and the same table name is being reference in SP. So if you will run your SP, it will work fine now as it will find the object but  SP_Depends won’t be able to find new object as new “baseTable” would have different Object_ID than older one.
Surprised!!!!!…. No, you shouldn’t if you have tried this before….
Now, to make reference of new “BaseTable” with ”usp_getBaseTableData”, you have to drop SP and recreate again. But this shouldn’t be the practical solution and that is why you have “SP_RefreshSQLModule” to refresh non schema-bound SPs or functions or views. Let us see how it works.
–create one table
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–create one SP to return data of baseTable
create proc usp_getbaseTableData
as
select * from baseTable
GO
–check whether SP_Depends returns any data or not
–I am sure, it will return :)
sp_dependsusp_getbaseTableData
GO
–now drop the basetable
drop table baseTable
GO
–now if you try sp_depends, it will show you below message as you don’t “BaseTable”
–Object does not reference any object, and no objects reference it.
sp_dependsusp_getbaseTableData
GO
–now create base table again with same structure
create table baseTable
(
ID INTIDENTITY(1,1)
)
GO
–use the same command, it will again show you same message.
–though you have now table, if you will run your SP, it will work
–but sp_depends will not show you proper information
sp_dependsusp_getbaseTableData
GO
–now you have two ways
–1.) drop and re-create SP
–2.) use sp_refreshsqlmodule.
EXEC sp_refreshsqlmodule ‘usp_getbaseTableData’
GO
–now you will again get proper information
–after taking any of the previous suggestion in comment
sp_dependsusp_getbaseTableData
GO
BTW, personally I prefer “information_schema.routines” then “SP_Depends” to get dependency of object.
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah