Query To get all the tables in Database

SELECT *
FROM sys.objects
WHERE type = 'u'

-------------------------------
Where 'u' stands for USER_TABLE
Where 'P' stands for SQL_STORED_PROCEDURE
Where 'TR' stands for SQL_TRIGGER



-------------------------------

To show all the store procedures in a database

SELECT name
FROM sys.objects
WHERE type = 'P'


Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.
-------------------------------------------------------------------------------------
SELECT *
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date,GETDATE()) < 7

Concatinating Multiple column value into a single column

Concatinating Multiple column value into a single column

SELECT a.ID 'PropertyID', a.Heading,RateSummary AS Description,c.propertyphotopath,pm.Photos,
pm.Visitors,pm.Inquiries,pm.PublishedDate,a.ExpiryDate
,Case pm.Photos when 0 then '' else 'Currently with'+' '+convert(varchar(50),pm.Photos)+' '+'Photos' End+','
+Case pm.Inquiries when 0 then '' else convert(varchar(50),pm.Inquiries)+' '+'Inquiries Since'+' '+convert(varchar(50),FirstEnquiryDate,106)+',' End
+Case pm.Visitors when 0 then '' else convert(varchar(50),pm.Visitors)+' '+'Visitors Since'+' '+Convert(varchar(50),FirstVisitedDate,106)+',' end
+Case DATEDIFF(day,,a.ExpiryDate) when 0 then '' else 'Expires in '+ ' '+convert(varchar(50),DATEDIFF(day, a.createdDate,a.ExpiryDate))+' '+'Days on'+' '+convert(varchar(50),a.ExpiryDate,106) end
+case isnull(pm.PublishedDate,0) when 0 then '' else ' '+'First Published on'+convert(varchar(50),pm.PublishedDate,106) end
FROM PropertyInfo a
INNER JOIN RateDetail b ON a.ID = b.PropertyInfoID
LEFT OUTER JOIN photogallery c ON a.ID = c.PropertyInfoID
Inner join propertymetrics pm on a.ID=pm.PropertyInfoID
WHERE c.ISthumbnail = 'TRUE';