Query to return database files and free space

Use below query to get database files and free space:

WITH C AS (
SELECT G.Name AS [FileGroupName],
       S.Type_Desc,
       S.Physical_Name AS [FileName],
       S.Size * CONVERT(FLOAT, 8) AS [Size],
       CAST(CASE S.Type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(S.Name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM Sys.filegroups AS G
INNER JOIN Sys.Master_Files AS S ON (( S.Type = 2 OR S.Type = 0)
                                      AND S.database_ID = DB_ID()
                                      AND (S.drop_lsn IS NULL))
                                    AND (S.Data_Space_ID = G.Data_Space_ID)
)
SELECT *, [Size] - [UsedSpace] AS RemainingSpace FROM C

SQL Server Cannot resolve collation conflict for equal to operation

Few days ago I had copied some tables, Stored Procedures, Views etc  from one database to a new database. 

Now whenever I execute a Stored procedure, I got below error:

"Cannot resolve collation conflict for equal to operation."

Thre error clearly indicates that the collation types for the columns being joined in the sql statement is different.

I had overcome this error by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.

SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT

Query to find Computed columns

Use below query to find all computed columns in a database:

SELECT O.NAME AS [Table], C.NAME AS [Column], T.TEXT AS [formula]
FROM SysColumns AS C, SysObjects O, SysComments T
WHERE C.ID = O.ID AND O.ID = T.ID AND C.ColID = T.Number AND Iscomputed = 1 AND O.type = 'U'

SQL Server blocked access to statement OPENROWSET / OPENDATASOURCE

Sometime you may get below error when you execute query using OPENROWSET statement:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

This error is coming because Ad Hoc Distributed Queries configuration parameter is disabled. To enable Ad Hoc Distributed Queries you had to execute below commands:

sp_configure 'show advanced options',1  
reconfigure 
sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure 

After executing above commands your problem will be solved.

You can do above changes in following ways also:

Right Click in SSMS on the SQL Server instance from the object explorer pane and choose Facets from the drop down menu.

In the facets window change the Facet to Surface Area Confuguration and set the AdHocRemoteQueriesEnabled to True

Find last execution time of Stored Procedure

SQL Server stores the execution plan of statements before executing any statement.In order to find the last execution we just need to query the SYS.dm_Exec_query_stats DMV.

SELECT ST.Text AS SQL, QS.creation_time,QS.lASt_Execution_time,QP.dbID,QP.ObjectID
FROM SYS.dm_Exec_query_stats AS QS
CROSS APPLY SYS.dm_Exec_sql_Text(QS.sql_handle) AS ST
CROSS APPLY SYS.dm_Exec_Text_query_plan(QS.plan_handle,DEFAULT,DEFAULT) AS QP WHERE ST.Text like '%<SP_Name>%'