Granting execute permission on all stored procedure in SQL

In SQL many times we need to provide execute permission to all stored procedure for specific user for any database. To achieve this you can make the execute statement by below query.

The query for it is just simple:

SELECT 'Grant Execute on ' + name +  ' SQLServerUSERName'FROM sysobjects WHERE xtype IN ('P')

For tables and views:

SELECT 'Grant select,insert,update,delete on ' + name + ' SQLServerUSERName''
from sysobjects where xtype in ('U','V')'

Enjoy.

Getting Month name from any date

Getting Month name from any date in SQL:

Just use the below query and you will get the result.

SELECT DATENAME(MONTH, DATEADD(MONTH, MONTH(GETDATE()), -1 ))


Example with result:

Done.

Getting Date from Date Time column

While using date time fields in WHERE clause you can face one common problem. Most of time you can't get the records for which you have used the datetime in where clause. For example:

SELECT * FROM tTable WHERE DateTimeColumn='12/24/2012'

In this I don't get the records which have date 12/24/2012, it is because in table the column contains the time also. Then how to GET these records. Its very simple just convert your date time column with below code:

CAST(FLOOR( CAST( SM.DateDownloaded AS FLOAT ) )AS DATETIME) AS DownloadedDate

Converting rows to columns

Using Pivot you can easily convert rows to columns.

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) p
PIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )
) AS 
ORDER BY VendorID

Enjoy.

Finding foriegn key table names which primary key is using in other tables

Finding table names in which a primary key of any table is used as foriegn key.

SELECT
    ConstraintName = fk.name,
    TableName = t.name,
    ColumnName = c.nameFROM
    sys.foreign_keys fkINNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_IDINNER JOIN 
    sys.tables t ON fk.parent_object_id = t.OBJECT_IDINNER JOIN 
    sys.columns c ON fkc.parent_object_id = c.OBJECT_ID AND fkc.parent_column_id = c.column_idINNER JOIN 
    sys.tables tref ON fk.referenced_object_id = tref.OBJECT_IDINNER JOIN 
    sys.columns cref ON fkc.referenced_object_id = cref.OBJECT_ID AND fkc.referenced_column_id = cref.column_idWHERE
    tref.Name = 'tCurrencyCode'
    AND cref.Name = 'CurrencyId'

Enjoy.