Find and remove duplicate rows - WITH CTE

with CTE as (

select

 col1
      ,col2
      ,col3
      ,col4
      ,col5
      ,col6

  ,RN = ROW_NUMBER()OVER(PARTITION BY  col1 ,col2  ORDER BY col1,col2)

  FROM [database].[schema].[table]
)
--select * from cte where RN >1

delete from cte where RN >1

Find only data between quotes (')

 select


SUBSTRING(RIGHT(inputtext,LEN(inputtext)-CHARINDEX('''',inputtext))
,0, CHARINDEX('''', RIGHT(inputtext,LEN(outputtext)-CHARINDEX('''',inputtext)) ) )

 from table

Find Useful information about your SQL Server

The below will find various useful chunks of information  about SQL Including licence information, version and cluster.  All using the SERVERPROPERTY command.

Run this:-


SELECT
@@SERVERNAME as InstanceName,


SERVERPROPERTY('BuildClrVersion')
,
 SERVERPROPERTY('Collation')
,
 SERVERPROPERTY('CollationID')
,
 SERVERPROPERTY('ComparisonStyle')
,
 SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
,
 SERVERPROPERTY('Edition')
,


 SERVERPROPERTY('InstanceName')
,
 SERVERPROPERTY('IsClustered')
,
 SERVERPROPERTY('IsFullTextInstalled')
,
 SERVERPROPERTY('IsIntegratedSecurityOnly')
,
 SERVERPROPERTY('IsSingleUser')
,
 SERVERPROPERTY('LCID')
,
 SERVERPROPERTY('LicenseType')
,
 SERVERPROPERTY('MachineName')
,
 SERVERPROPERTY('NumLicenses')
,
 SERVERPROPERTY('ProcessID')
,
 SERVERPROPERTY('ProductVersion')
,
 SERVERPROPERTY('ProductLevel')
,
 SERVERPROPERTY('ResourceLastUpdateDateTime')
,
 SERVERPROPERTY('ResourceVersion')
,
 SERVERPROPERTY('ServerName')
,
 SERVERPROPERTY('SqlCharSet')
,
 SERVERPROPERTY('SqlCharSetName')
,
 SERVERPROPERTY('SqlSortOrder')
,
 SERVERPROPERTY('SqlSortOrderName')
,
 SERVERPROPERTY('FilestreamShareName')
,
 SERVERPROPERTY('FilestreamConfiguredLevel')
,
 SERVERPROPERTY('FilestreamEffectiveLevel')

Find where Password and Username are the same

Weak security is always going to bite you.  The first thing any Hacker, Auditor, Penetration Tester or RAS is going to try is passwords that are the same as the user.

So how can we check this?  well a handy little script that uses PwdCompare, to checked the hashed password with the user name.

here it is:-

use master

select
cast(@@SERVERNAME as varchar(150)) as SQLInstanceName
,name as [LoginName]
,'Password is same as Login Name' [Description]
from syslogins
WHERE PWDCOMPARE (name,password) = 1

Powershell to do almost any thing

Below is a powershell script that will let you read servers from a table:-



CREATE TABLE [Core].[InstanceDatabases](
[DatabaseID] [bigint] IDENTITY(1,1) NOT NULL,
[InstanceID] [bigint] NOT NULL,
[DatabaseName] [varchar](50) NULL,
[RemoteDBID] [int] NULL,
[sysEffectiveFromDate] [datetime] NULL,
[sysEffectiveToDate] [datetime] NULL,
[Active] [bit] NULL,
 CONSTRAINT [PK_Instance_Databases] PRIMARY KEY CLUSTERED
(
[DatabaseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Core].[InstanceDatabases]  WITH CHECK ADD  CONSTRAINT [FK_InstanceDatabases_InstanceDetails] FOREIGN KEY([InstanceID])
REFERENCES [Core].[InstanceDetails] ([InstanceID])
GO

ALTER TABLE [Core].[InstanceDatabases] CHECK CONSTRAINT [FK_InstanceDatabases_InstanceDetails]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT (getutcdate()) FOR [sysEffectiveFromDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ('2099-01-01') FOR [sysEffectiveToDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ((0)) FOR [Active]
GO

In here can be stored all the servers you would like to connect to.

You will also require a few external scripts from other devs:-

http://poshcode.org/4967

http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

You can then run any SQL on all boxes in your estate and place the answer back to a table.

Code:-


##########
Dave Dunckley
Version 1.1
Please fell free to use as your like, please post back any modifications.
############

. D:\Powershell\TSM\Plugins\invoke-sqlcmd2.ps1

. D:\Powershell\TSM\Plugins\Out-DataTable

cls

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$wmiquery = "select SystemName, Caption, DeviceID, DriveType, FileSystem, FreeSpace, Size from win32_LogicalDisk where DriveType = 3"


########################################
# Set Config Settings for this session #
########################################
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100

################################
# Setup and populate variables #
################################

# TSM Monitoring Server
$ManagementServer="servrename"

# DBA Monitoring DB
$ManagementDB="Monitor"

# OutCSV file
$CSVOUT = 'D:\Powershell\TSM\Temp\DBLastUsed.csv'

# Query String for servers to Collect Info from
$q = "SELECT distinct upper(InstanceName) FROM $ManagementDB.Core.InstanceDetails WHERE [InstanceScan] = 1 ORDER BY 1;"

# Query Conection String
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Initial Catalog=$MonitoringDB; Trusted_Connection=true");

# Open Connection
$sconn.Open()

# Open Data Adapter and create data table
$da = new-object System.Data.SqlClient.SqlDataAdapter ($q, $sconn)
$dt = new-object System.Data.DataTable

#Fill Data table
$da.fill($dt)

# Set Stage table name for Truncate
$StageTable = "DBLastUsed"

# Truncate Stage Table
Invoke-Sqlcmd –ServerInstance $ManagementServer –Database $ManagementDB –Query "truncate table stage.$StageTable"

#Loop though each server


foreach ($row.host_connection in $dt.Rows)
{
$Server = $($Row[0])
   
 
#run SQL
      invoke-sqlcmd2 -ServerInstance $Server -Database "master" -Query "
   
SELECT @@servername as servername, DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2"   |Export-CSV -NoTypeInformation $CSVOUT


#Output to temp CSV

$csvDataTable = Import-CSV -Path $CSVOUT | Out-DataTable


#Create conection

$ConnectionString = "Data Source=$ManagementServer; Database=$ManagementDB; Trusted_Connection=True;";

#Import from temp CSV directly to SQL

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
$bulkCopy.DestinationTableName = "stage.$StageTable"
$bulkCopy.WriteToServer($csvDataTable)
   



#close connections

$connSQLOUT.Open()


$cmd = $connSQLOUT.CreateCommand()
   
   
 
   
   $connSQLOUT.Close()
   
   
   


}

#enjoy!

Find when a database was last used in SQL

Here is some nice SQL that will tell us when each database was last accessed.

SELECT @@servername , DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Get useful information on your database

Its sometimes useful to know a little more about your database, the below will give you information on Database_name, File_Name, Location, FileSizeMB, SpaceUsed, SpaceFree, database Owner, Recovery Model, Compatibility and Collation:-

set nocount on
create table #dbfileInfo(
Database_name varchar(300),
File_name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2),
DB_Owner varchar(max),
Recovery_Model varchar(max),
Compatibility varchar(max),
Collation varchar(max))

declare @mySQL nvarchar(2000)
--DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR
DECLARE @DBO varchar(max)
DECLARE @Rec_Model varchar(max)
DECLARE @compt_lvl varchar(max)
Declare @collation varchar(max)


set nocount on
declare @SQLText nvarchar(4000), @DBName sysname


select @DBName = min(name) from sysdatabases
while @DBName is not null
begin



select @mySQL =
    'use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select db_name(),
      sf.name
    , filename
    , convert(decimal(18,2),round(sf.size/128.000,2)) as FileSizeMB
    , convert(decimal(18,2),round(fileproperty
(sf.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(18,2),round((sf.size-fileproperty
(sf.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
, SUSER_SNAME(owner_sid) as [DB_Owner]
    ,recovery_model_desc
    ,compatibility_level
    ,Collation_name
    from master.dbo.sysaltfiles sf
 INNER join master.sys.databases sd
 ON sf.dbid = sd.database_id where sd.name = db_name()
    '
    exec sp_executesql @mySQL
      -- select  @mySQL

--select @DBName
select @DBName = min(name) from sysdatabases where name > @DBName
end

 select * from #dbfileInfo
drop table #dbfileInfo