How to find the No. of tables list in SQL Server Database

I know few way to find the no. of tables in SQL Server Database.

1. Using  INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT *FROMINFORMATION_SCHEMA.TABLESwhereTABLE_CATALOG='Thirmal'

2. Using sys.tables SELECT *FROM sys.tables

3. Using sysobjects

SELECT *FROMsysobjects WHERE xtype = 'U'

Computed Columns in SQL Server

Computed columns are derived columns that are bound to values of other columns. However the datatype of these computed columns depends on the nature of the end result. They may be bound to columns of either the same datatype or they could be bound to columns of different datatypes.

Consider the following set of data

drop table #Temp_Computed

create table #Temp_Computed (Column1 int, computed_Column1 as Column1, computed_date as dateadd(day,Column1,getdate()))

insert into #Temp_Computed (Column1)

select 5

select * from #Temp_Computed

Output

As you can see, the datatype of computed_id will be same as that of the ID as ID is directly used in the computed column definition. However the datatype of the columncomputed_date will be datetime because the expression dateadd(day,id,getdate()) will do an implicit conversion to datetime datatype as getdate() is used in the definition.

Let us consider another set of data

drop table #Temp_Computed

create table #Temp_Computed

(

  Column1 int

, computed_Column1 as Column1/2.0

, computed_date as (Column1*3300000000000)

)

insert into #Temp_Computed (Column1)

select 5

select * from #Temp_Computed

Output

As you can see, the datatype of computed_id will be of decimal type because of the expression id/2.0 which results to decimal number. The datatype of the columncomputed_numberf will be BIGINT because the expression id*300000000000 will do an implicit conversion to the BIGINT datatype as the result won't fit into a INT datatype

So the datatpye of computed column differs based on the expression and if you want to update the value returned by a computed column to another table, you need to make sure that the datatypes match each other. 

Keep these points in mind while using Computed columns in SQL Server

Watch Videos Here

Please give your valuable Comments below :) :) 

How to Display a variable value using Script Task in SSIS

Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.

If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.

Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.

Table script

create table tbl (ColA varchar(50), ColB varchar(50))

insert into tbl  values('Test Message','Hello How are You')

Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package

Follow the below steps 

Double click on SQL execute task

Create a data connection to the database where above table created

In SQL Statement add -select * from tbl

Select Resultset as Single Row as below Screenshot 

Go to Result tab in the Right side

Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot

Next connect the SQL Execute task to script task

Now, double click on Script task

Select Read only variable as User Message which we created above as below screenshot

      

Click on edit script and add Message box (MessageBox.Show(Dts.Variables["Message"].Value.ToString());) inside Main Function as below Screenshot

Save and Click ok 

Run your Package this will display your variable value in the Message box as below. 

Please write in Comments If you'r stuck with any step or need any help.

Facebook Rewards 10-year-old With $10,000 for Finding Instagram Bug

New York: The social networking giant has paid $10,000 to a 10-year-old boy for spotting a bug in Facebook-owned photo-sharing platform Instagram.

According to a report in technology website VentureBeat.com, Jani from Finland discovered the security flaw in Instagram on his own.

He found a bug in Instagram which requires you to be at least 13 before even signing up, that let him delete any comment on the social network.

"He reported the bug by email, offered proof by deleting a message on one of Facebook's test Instagram accounts and it was fixed in February. Facebook paid him the bug bounty in March," the report added.

"I would have been able to remove anyone, even Justin Bieber," the report quoted Jani as saying.

The Finnish boy wishes to become a security researcher. "It would be my dream job. Security is very important," he was quoted as saying.

He used the reward money to buy a new bike, football gear and computers for his two brothers.

Like Google and Microsoft, Facebook also has a bug bounty programme.

In February, Facebook announced that it had paid $4.3 million in rewards to more than 800 security researchers for over 2,400 submissions since launching its bug bounty programme in 2011.

In 2015, 210 researchers received $936,000 with an average payout of $1,780.

A jobless man applied for the position of 'office boy' at Microsoft. The HR manager interviewed him, then gave him a test: clean the floor. The man

A jobless man applied for the position of 'office boy' at Microsoft.

The HR manager interviewed him, then gave him a test: clean the floor. The man passed the test with flying colors.

"You are hired," HR manager informed the applicant, "give me your e-mail address, and I'll send you the application for employment, as well as the date you should report for work.

The man replied " I don't have a computer, or an email!"

"I'm sorry," said the HR manager. "If you don't have an email, that means you do not exist. And we cannot hire persons who do not exist."

The man was very disappointed.

He didn't know what to do. He only had $10 with him. Once that is spent, he won't have any money to buy any food.

He went to the supermarket and bought a crate of tomatoes with his $10.

He went from door to door and sold the tomatoes in less than two hours. He doubled his money.

He repeated the operation three times, and returned home with $60. He realized that he can survive

this way. He started to go everyday earlier, and return late.

He doubled or tripled his money every day. Soon, he bought a cart, then a truck. In a very short time, he had his own fleet of delivery vehicles.

Five years later, the man became one of the biggest food retailers in the U. S. He started to plan his family's future, and decided to have a life insurance.

He called an insurance broker, and chose a protection plan.

At the end of the conversation, the broker asked him for his email address.

The man replied: ' I don't have an email.'

The broker was dumbfounded. "You don't have an email, and yet have succeeded in building an empire. Can you imagine what you could have been if you had an email?," he exclaimed.

The man thought for a while, and replied, "an office boy at Microsoft!"

If you just lost your Job or Just failed an Interview Don't worry be Optimistic..... Good days are on the way and something better is reserved for you.

In this page i Request you to have a look at the inspiration. Sometimes they encourage us to seek for our dreams, trust life and ourselves and never give up.

They teach us to notice the magical beauty of the world that we live in and that surround you every day, as well as they show, what are the true values that are worth aiming for in our lives.

Also they tell about God's caring and the power of unconditional love.

We get inspired by the strength of the human spirit and we learn, how to be a better person, more sensitive, supportive, kind and loving.

Written By: Nishanth Varathakumar

SQL – Stuff Function

SQL - STUFF() Function

This is the most amazing function of T-SQL which is used to delete a specified length of characters within a string and replace with another set of characters.

Syntax:- STUFF (Character Expression, Start, Length, Replace With Expression) Arguments: This function uses the following parameters. Character Expression: Is an expression of character data. Character Expression can be a constant, variable, or column of either character or binary data. Start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first Character Expression, a null string is returned. Start can be of type bigint. Length: Is an integer that specifies the number of characters to delete. If length is longer than the first Character Expression, deletion occurs up to the last character in the last Character Expression. Length can be of type bigint. Replace With Expression: Is an expression of character data. Replace With Expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of Character Expression beginning at start.

Important points to remember: There are some basic points always keep in mind as given below- 

1. If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. 
2. If the start position is 0, a null value is returned. 
3. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

Important Facts - Unfortunately the stuff function only works on "strings" (char, nchar, varchar, nvarchar). If you need to use it on a numeric data type you will have to convert it to a string and back again.

Example 1: Generate a Comma-Separated List

Stuff function is very useful if we want to add comma-separated list. If we want to capture all comments against any particular topic then stuff function comes into the picture such as given below-

--- declare table variable to store the comments

DECLARE @UserInputs TABLE

(  

    PollId Int, PollSubject Varchar(250), UserComments Varchar(250)

)

----- Insert Values into table variable

INSERT INTO @UserInputs (  PollId, PollSubject, UserComments)

VALUES

(1, 'Most favourite  super hero?', 'Superman' ),

(1, 'Most favourite  super hero?' ,'Batman' ),

(1, 'Most favourite  super hero?' ,'Ironman'),

(1, 'Most favourite  super hero?' ,'Wolverine'),

(2, 'Most favourite  movie?', 'Titanic' ),

(2, 'Most favourite  movie?' ,'The Note Book' ),

(3, 'Most favourite  Game?' ,'Cricket'),

(3, 'Most favourite  Game?' ,'Football')

----- Table Variable output

SELECT PollId, PollSubject, UserComments FROM @UserInputs

PollId	PollSubject	UserComments
1	Most favourite  super hero?	Superman
1	Most favourite  super hero?	Batman
1	Most favourite  super hero?	Ironman
1	Most favourite  super hero?	Wolverine
2	Most favourite  movie?	Titanic
2	Most favourite  movie?	The Note Book
3	Most favourite  Game?	Cricket
3	Most favourite  Game?	Football

----- Comments by using stuff function

SELECT DISTINCT PollId, PollSubject,

UserInput=STUFF((SELECT ',' + UserComments

                                                        FROM @UserInputs

                                                        Where PollId=UI.PollId

                                                        ORDER BY PollSubject

                                                        FOR XML PATH('')), 1, 1, '')

from @UserInputs UI

ORDER BY UI.PollId

PollId	PollSubject	Output
1	Most favourite super hero?	Superman,Batman,Ironman,Wolverine
2	Most favourite  movie?	Titanic,The Note Book
3	Most favourite  Game?	Cricket,Football

We are aware that all that STUFF is doing is trimming the leading , off of the text that FOR XML PATH is generating.

Example 2: Insert One String Into Another String at a Specific Location

We can use the stuff function to replace or insert new string into the existing string as given below:

---- declare table variable to store the comments DECLARE @UserInputs TABLE (       PollId Int, PollSubject Varchar(250), UserComments Varchar(250) ) ----- Insert Values into table variable INSERT INTO @UserInputs (  PollId, PollSubject, UserComments) VALUES (1, 'Most favourite  super hero is ? for kids', 'Superman' ), (2, 'Most favourite  movie is ? in Cinema', 'Titanic'), (3, 'Most favourite  Game is ? in the world.' ,'Cricket') ----- Table Variable output SELECT PollId, PollSubject, UserComments FROM @UserInputs PollId PollSubject UserComments 1 Most favourite  super hero is ? for kids Superman 2 Most favourite  movie is ? in Cinema Titanic 3 Most favourite  Game is ? in the world. Cricket ----- Insert One String Into Another String at a Specific Location ----- by using stuff function SELECT PollId ,UserInputs=STUFF(PollSubject, CHARINDEX('?', PollSubject), 1, UserComments)  FROM @UserInputs  ORDER BY PollId PollId UserInputs 1 Most favourite  super hero is Superman for kids 2 Most favourite  movie is Titanic in Cinema 3 Most favourite  Game is Cricket in the world.

SQL Stuff () Vs REPLACE()

Stuff () - This function can be used for delete a certain length of the string and insert a new string in the deleted place.

STUFF Syntax: STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)

String - String to be overwritten

StartPos - Starting Position for overwriting

LengthofReplaceChar - Length of replacement string

ReplaceString - String to overwrite 

REPLACE()- This function replaces all the occurrences of a string expression with a new string within an input string.  

REPLACE Syntax: REPLACE (String, StringToReplace, StringTobeReplaced)

String - Input String

StringToReplace - The portion of string to replace

StringTobeReplaced - String to overwrite

Conclusion

The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.