Microsoft云工程师 - SharePoint,Office 365,Azure,Dotnet,Agenular,JavaScript。
Microsoft云工程师 - SharePoint,Office 365,Azure,Dotnet,Agenular,JavaScript。

TSQL.

视频–具有Easy TSQL存储过程的同步表

我需要通过匹配主键来合并两个数据表,然后填充所有其他列。  下面的TSQL代码将在SQL中快速且轻松地完成该码。  无需远程客户端PowerShell Dot Net。  希望你能找到这个有用的。  Cheers! 

shades_smile.

视频

代码

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

截图

图像
图像
图像
图像

参考

使用RowCounts和Storage测量SQL表

使用自定义SQL业务数据库,它可以有助于枚举每个表和行计数。  下面的TSQL将为该目的创建一个名为“RowCounts”的新视图。  我建议将此添加到任何自定义SQL数据库中,以便于数据质量检查和监控。

干杯!  

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

截屏

图像

参考

TSQL..–设置所有数据库简单恢复

使用以下TSQL脚本将所有数据库设置为简单恢复模式。  有助于为开发环境和实验室VM保存在磁盘存储和备份空间上。

干杯! 

shades_smile.

代码

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
返回顶部▲返回顶部▲