You are here

tsql

TSQL – Get Number Of Cores & MaxDOP In Single Query

This is a handy script to show the number of cores and MaxDOP in a single output, along with a built-in script to change the value. The built-in script shows up as a query result so you can copy/paste it.

SELECT cpu_count AS [Cores] 
        , (SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism') AS [MaxDOP]
        , '/*Next Line For SQLCMD Mode*/
:CONNECT ' + @@SERVERNAME + '
sp_configure ''max degree of parallelism'', ''VALUE''; 
RECONFIGURE;
GO
        ' AS [Configure Script]
FROM [sys].[dm_os_sys_info]

TSQL – Orphaned User Script

Finding Orphaned Users

USE [db]
GO
EXEC sp_change_users_login 'Report'
GO

Fixing Orphaned Users

USE [db]
GO
EXEC sp_change_users_login 'auto_fix', 'USERNAME'
GO

Finding Duplicate Records In Database

SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
FROM TABLE_NAME
GROUP BY COL_TO_CHECK
HAVING COUNT(COL_TO_CHECK) > 1
If there is more than 1 column with that value it will show up like:
COL_TO_CHECK               COUNT(COL_TO_CHECK)
-------------------------------------------------------------
Value                             3
Value2                            2
Value99                           4

TSQL – Simple Loop Through Result Set Using Cursor

This is the basic query text to use whenever you need to loop through a result set in SQL Server using TSQL. In this example we are getting every database name and printing it out.

TSQL – Loop Through Result Set WITHOUT Cursors

There is a time and a place for everything, cursors included. Here is one way to avoid using them.

To NOT use cursors, you must create a temp table with a "flag" column to indicate whether or not it has been processed yet. Then, rather than updating the cursor, you update the flag column. Here's an example:

Rolling Back Dynamic SQL

Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code:

No Transactions

You can see the third row having a value of "5", showing that it was updated

SQL Server - Download all RDL files from Report Server

---- Allow advanced options to be changed.
--EXEC sp_configure 'show advanced options', 1
--GO
---- Update the cuently configured value for advanced options.
--RECONFIGURE
--GO
---- Enable xp_cmdshell
--EXEC sp_configure 'xp_cmdshell', 1
--GO
---- Update the cuently configured value for xp_cmdshell
--RECONFIGURE
--GO
---- Disallow further advanced options to be changed.
--EXEC sp_configure 'show advanced options', 0
--GO
---- Update the cuently configured value for advanced options.
--RECONFIGURE
--GO
 
--Replace NULL with keywords of the ReportManager's Report Path,

Pages

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
1 + 14 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer