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 ‘tableName’

For example I am using Adventureworks2012 and I want to know the dependencies of product table then I have to write following command

Use AdventureWorks2012
Go
EXEC sp_msdependencies '[Production].[Product]'
GO

When I run this command I get result as shown in below figure

I hope this tip may help you somewhere.

Thanks for reading.

Enjoy !!!

Rj !!!

How explicitly insert default value ? TIP #72

 

Suppose, sometimes you just need  entry in the table with all the default values.

For example you have a Student table which looks like as below

CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),
                          FirstName VARCHAR(100) DEFAULT 'RAJAT',
                          Course    VARCHAR(100) DEFAULT 'MATHS')

Now , suppose for in some situation we need just default entry for example in my case I am just trying to insert 100 rows with default value so I need to write below statement

INSERT INTO #tmpStudent (FirstName ,Course ) VALUES (Default, default)

Now when I run the above statement. It insert default value of FirstName,course

I hope this might be helpful to you somewhere.

Enjoy!!!

RJ!!

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 #tmpStudentStructure
FROM tblStudentSource
WHERE 1= 0

in the above query we want to copy structure of tblStudentSource.

see below snap which help you to understand it more

I hope this might help you if some asked you this question.

Thanks & Enjoy!!!

RJ!!!