Find all dependent objects of an Object

You can use this system Stored Procedure to find the all dependencies of an Object.

EXEC sp_depends @objname = N'StoredProcedureName' ;

How to check the SQL Server Varsion, Name & Edition

WE can see the Sql Server Name, version, edition etc using below query:


SELECT @@SERVERNAME , 
SERVERPROPERTY('productversion'), 
SERVERPROPERTY ('productlevel'), 
SERVERPROPERTY ('edition')

View all Database File Sizes

SELECT s_mf.[name], s_mf.physical_name, s_mf.[size]/128 AS SizeInMB, s_mf.max_size/128 AS MaxSizeInMB, s_mf.growth
FROM sys.master_files s_mf
ORDER BY s_mf.[size] DESC

Above query will return Database Name, its physical path & database files size in MB.

Happy Coding !!

How to Encrypt Stored procedures in Sql Server

At times, it is needed that you encrypt the text of stored procedures containing sensitive information. SQL Server provides the WITH ENCRYPTION to encrypt the text of the stored procedure.

CREATE procedure [dbo].[TestProc]
WITH ENCRYPTION
AS
BEGIN
SELECT 'TEST' as TestColumn
END

Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:

EXEC sp_helptext TestProc

'The text for object 'Ten Most Expensive Products Encyrpt' is encrypted.'

One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.