Count number of tables,sps,function or views exist in Database

/* Count Number Of Tables In A Database */
SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

/* Count Number Of Views In A Database */
SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS 

/* Count Number Of Functions In A Database */
SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' 

/* Count Number Of Stored Procedures In A Database */ 
SELECT  COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

LEFT JOIN with same table

Today i come across a situation where my friend want to show parent column value near to child calumn so he is writing a subquery there. I write the following query

FROM 
TabelA As Parent
Left JOIN
TabelA As Child
On Parent.Id = Child.ParentID

After this query he is getting the perfect result. Avoid the subquery whereever it is possible because it hits the performance badly.

After this query he is writing case statement to append the parent column value in child calumn. I have replace that case statement with followiing

SELECT
Child.Value + ' ' + ISNULL(Parent.Value,'')
FROM 
TabelA As Child
Left JOIN
TabelA As Parent
On Parent.Id = Child.ParentID

Always try to use the SQL in-built function