Differences Between the SQL-based and Fetch-based Reports

Area	SQL-based Report
Data Provider	The <DataProvider> element value in the .rdl file is set to SQL. For example: <DataProvider>SQL</DataProvider>
Report query	The query specified for retrieving data is in the <CommandText> sub-element under the<Query> element in the report definition (.rdl file) is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be: <CommandText>SELECT name FROM FilteredAccount;</CommandText>

Area	Fetch-based report
Data Provider	The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example: <DataProvider>MSCRMFETCH</DataProvider>
Report query	The query specified for retrieving data is in the <CommandText> sub-element under the <Query>  element in the report definition (.rdl file) is a FetchXML query. For example, the query for retrieving  all account names for a Fetch-based report will be: <CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt;     &lt;entity name="account"&gt;         &lt;attribute name="name" /&gt;     &lt;/entity&gt; &lt;/fetch&gt;</CommandText>

Get all the tables from the SQL database

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, 
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME

Get the data from all the tables

USE AIRLINE --DB NAME
GO
DECLARE @COLUMNNAME AS VARCHAR(50) = 'PNAME' --THE NAME OF THE COLUMN ON WHICH YOU NEED TO PUT THE CRITERIA
DECLARE @CRITERIA AS VARCHAR(50) = 'CONVERT(DATE,' + @COLUMNNAME + ') >= ''20130225''' -- THE ACTUAL CRITERIA/WHERE CLAUSE OF THE QUERY

PRINT @COLUMNNAME
PRINT @CRITERIA

SELECT 
  'SELECT ''' + T.NAME + ''' AS  TABLENAME, * FROM ' + T.NAME + ' WHERE ' + @CRITERIA 
FROM 
  SYS.COLUMNS C
INNER JOIN SYS.TABLES T
  ON T.OBJECT_ID = C.OBJECT_ID   
WHERE 
  C.NAME = @COLUMNNAME

Last Execution Date Time of a Stored Procedure

USE Airline
GO

SELECT 
  O.NAME,
  PS.LAST_EXECUTION_TIME
FROM 
  SYS.DM_EXEC_PROCEDURE_STATS PS 
INNER JOIN SYS.OBJECTS O 
  ON O.[OBJECT_ID] = PS.[OBJECT_ID] 

To get a list of Databases that were backed-up and do not currently exist

SELECT
  DISTINCT B.DATABASE_NAME
FROM
  MSDB.DBO.BACKUPSET B
WHERE
  DB_ID(B.DATABASE_NAME) IS NULL

To get the List of all Databases which are not backed up till date

SELECT
  D.NAME [DB_NAME]
FROM
  MASTER.SYS.DATABASES D
LEFT JOIN MSDB.DBO.BACKUPSET B
  ON B.DATABASE_NAME = D.NAME
WHERE
  D.DATABASE_ID IS NULL

To get the List/History/Log of all the Successful Backups

SELECT 
  B.MACHINE_NAME,
  B.SERVER_NAME,
  B.DATABASE_NAME AS DBNAME,
  B.BACKUP_START_DATE,
  B.BACKUP_FINISH_DATE,
  CASE 
    WHEN B.[TYPE] = 'D' THEN 'DATABASE'
    WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
    WHEN B.[TYPE] = 'L' THEN 'LOG'
    WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
    WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
    WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
    WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
    ELSE B.[TYPE]
  END BACKUP_TYPE,    
  B.EXPIRATION_DATE,
  B.[USER_NAME],
  DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
  B.RECOVERY_MODEL,
  B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
  BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM 
  MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
  ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
ORDER BY 
  B.BACKUP_START_DATE DESC
GO