Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

TSQL

VIDEO – Sync tables with easy TSQL Stored Procedure

I needed to merge two tables of data by matching primary key and then populating all other columns.   The below TSQL code will accomplish that quickly and easily native within SQL.   No need for remote client side PowerShell Dot Net.   Hope you find this helpful.  Cheers! 

shades_smile

Video

Code

CREATE PROCEDURE [dbo].[Sync] AS 
--Insert new rows
PRINT 'INSERT'
INSERT INTO [States]
([Name])
SELECT 
[Name]
FROM [States-Temp]
WHERE [Name] NOT IN (SELECT [Name] FROM [States])
--Delete excess rows
PRINT 'DELETE'
DELETE FROM [States] 
WHERE [Name] NOT IN (SELECT [Name] FROM [States-Temp])
--Update matching rows
PRINT 'UPDATE'
UPDATE t2 
SET t2.Abbr = t1.Abbr,
t2.Population = t1.Population,
t2.Capital = t1.Capital
FROM [States] AS t2
INNER JOIN [States-Temp] AS t1 
ON t2.[Name]=t1.[Name]
GO

Screenshots

image
image
image
image

Reference

Measure SQL tables with ROWCOUNTS and STORAGE

With custom SQL line of business databases it can be helpful to enumerate all tables and row counts for each.   The below TSQL will create a new VIEW named “RowCounts” for that purpose.   I’d suggest adding this to any custom SQL database for easy data quality checks and monitoring.

Cheers!  

shades_smile

TSQL RowCount

CREATE VIEW RowCounts AS (
SELECT o.NAME,
  i.rowcnt 
FROM sysindexes AS i
INNER JOIN sysobjects AS o
ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0)

TSQL Storage

SELECT 
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
sys.tables t
INNER JOIN      
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
sys.schemas s ON t.schema_id = s.schema_id
WHERE 
t.NAME NOT LIKE 'dt%' 
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY 
t.Name, s.Name, p.Rows
ORDER BY 
t.Name

Screenshot

image

References

TSQL – Set all databases SIMPLE recovery

Use the below TSQL script to set all databases to simple recovery mode.   Helpful for development environments and lab VMs to save on disk storage and backup space.

Cheers! 

shades_smile

Code

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
    begin
	
    select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='USE @dbname; DBCC SHRINKFILE (N''@dbname_log'' , 0, TRUNCATEONLY)'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    fetch next from c1 into @dbname
    end
close c1
deallocate c1

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲