Change MAXDOP (Maximum Degree of Parallelism) SQL Server

USE AdventureWorks;
GO
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ‘max degree of parallelism’, 12;
GO
RECONFIGURE WITH OVERRIDE;
GO

Advertisements

Powershell Script to Output All Tables in a Database to CSV for SQL Server


$server = "SQLCON-TEST02"
$database = "AdventureWorks"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
$queryData = "SELECT * FROM [$($Row[0])]"

#Specify the output location of your dump file
$extractFile = "E:\PROJECTS\$($Row[0]).csv"

$command.CommandText = $queryData
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
}

#####################################################################

Run PowerShell
To run PowerShell from SQL Server Management Studio
Open Object Explorer.
Navigate to the node for the object to be worked on.
Right-click the object and select Start PowerShell.

Copy one column to another sqlserver

UPDATE table SET columnB = columnA

–This will update every row.

UPDATE table_name SET
destination_column_name=orig_column_name
WHERE condition_if_necessary

–This will update rows which satisfy the WHERE clause

UPDATE table1
SET column1 = (
SELECT column2
FROM table2
WHERE table2.id = table1.id
);
Whereby:

table1 = table that has the column that needs to be updated
table2 = table that has the column with the data
column1 = blank column that needs the data from column2 (this is in table1)
column2 = column that has the data (that is in table2)

 

–This will update one column based on value of another table column

 

 

 

List all Highly Fragmented Indexes in SQL Server

IF EXISTS ( SELECT *
FROM [tempdb].[dbo].[sysobjects]
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
[DatabaseName] [nvarchar] (100) NULL,
[ObjectName] [nvarchar] (100) NULL,
[Index_id] INT,
[indexName] [nvarchar] (100) NULL,
[avg_fragmentation_percent] float NULL,
[IndexType] [nvarchar] (100) NULL,
[Action_Required] [nvarchar] (100) default 'NA'
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlQuery = '
USE [' + @dbname + '];

IF EXISTS
(
SELECT compatibility_level
FROM sys.databases
WHERE
name = N'''+ @dbname +'''
AND compatibility_level >= 90
)
BEGIN
INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
(
DatabaseName
, ObjectName
, Index_id
, indexName
, avg_fragmentation_percent
, IndexType
)
SELECT
db_name() as DatabaseName
, OBJECT_NAME (a.object_id) as ObjectName
, a.index_id, b.name as IndexName
, avg_fragmentation_in_percent
, index_type_desc
FROM
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON
a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE
b.index_id <> 0
AND avg_fragmentation_in_percent <> 0
END;'

EXEC sp_executesql @sqlQuery

FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Rebuild'
WHERE avg_fragmentation_percent >30
GO

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Reorganize'
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename

Inner Join in SQL Server

Tables are

a userbasic

b userlmits

c groupauthclass

— 2 Table Inner Join SQL Server —


select a.userid,a.groupid,a.username,a.branch, b.onlinedebit,b.offlinedebit
from dbo.userbasic a join dbo.userlimits b on a.userid=b.userid ;

— 3 Table Inner Join SQL Server —


select a.userid,a.groupid,c.classid,c.authority, b.onlinedebit,b.offlinedebit
from dbo.userbasic a join dbo.userlimits b on a.userid=b.userid
join dbo.groupauthclass c on c.groupid=a.groupid;

Truncate Log in Database Mirroring SQL Server 2008 2012

select log_reuse_wait_desc from sys.databases where name = ‘AdventureWorks’

If Output is “LOG_BACKUP”

— Execute 2 Times just in case —

BACKUP LOG AdventureWorks TO DISK=’NUL:’

— Now Truncate the Log —

USE AdventureWorks
GO
ALTER DATABASE AdventureWorksSET RECOVERY SIMPLE WITH NO_WAIT;
GO
DBCC SHRINKFILE(AdventureWorks_LOG, 100);
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT;
GO

Delete Duplicate Rows SQL Server

1. Add an Identity Column to the Table

alter table MASTER
add identity_col int identity(1,1);

2. Delete duplicate data using Below Query (In the GROUP BY CLAUSE you can specify multiple columns if the row is indetified using more than one field)

delete from MASTER where identity_col not in ( select Min(identity_col) from MASTER group by trnx_uid);