SQL scripts

Here are some useful SQL scripts:

All table row count

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

Database - clean table

Example - Table: ContentItems

delete  contentItems where id in(
select top(100000) cpi.id from contentItems cpi
join contentpageversions cpv on cpv.id = cpi.contentpageversionid
join ContentPages cp on cpv.contentpageid = cp.id
where cpv.publishdate is null and cp.pagetype = 7
and cpv.id not in (select max(id) from contentpageversions GROUP BY contentpageid))
WHILE (@@rowcount > 0)
BEGIN
delete  contentItems where id in(
select top(100000) cpi.id from contentItems cpi
join contentpageversions cpv on cpv.id = cpi.contentpageversionid
join ContentPages cp on cpv.contentpageid = cp.id
where cpv.publishdate is null and cp.pagetype = 7
and cpv.id not in (select max(id) from contentpageversions GROUP BY contentpageid))
END

Database backup

exec msdb.dbo.rds_backup_database
@source_db_name='#DATABASENAME#',
@s3_arn_to_backup_to='arn:aws:s3:::#FILEPATH&NAME#.bak',
@overwrite_S3_backup_file=1,
@type='FULL';

Database restore

exec msdb.dbo.rds_restore_database @restore_db_name='#DATABASENAME#', 
@s3_arn_to_restore_from='arn:aws:s3:::#FILEPATH&NAME#.bak';

reference

Check task status

exec msdb.dbo.rds_task_status @db_name='DATABASENAME';

Check database size

EXEC sp_spaceused;

Search table for specific string
SELECT * FROM [##DBNAME##].[dbo].[##TABLENAME##] WHERE 
Content LIKE '%##string##%';

Search & update sub-string

UPDATE [##DBNAME##].[dbo].[##TABLENAME##] 
SET Content = REPLACE(Content, '##originalstring##', '##newstring##') WHERE Content LIKE '%##originalstring##%';