SQL Samples that come from 2019 testing for DNS management

/* dnsRecords2 works and the ps1 associated does not need ODBC entry, integrated security is fine */
create database dbDNS
use dbDNS

-- rename the 1 to 2 in DNSRecords to add more tables
-- drop table DNSRecords2

create table DNSRecords2 (
    row_id integer identity,
    --ztimestampvalue integer null,
    domain_name varchar(255) null,
    --domainnamevalue nchar(255) null,
    recordtype varchar(255) null,
    --recordtypevalue nchar(100) null,
    ipaddress varchar(255) null,
    --ipaddressvalue nchar(100)
);

-- alter table after bcpload by p.ps1

sp_columns DNSRecords2
GO
sp_columns DNSRecords2
GO

alter table DNSRecords2
add homepagetext nvarchar(max) null

alter table DNSRecords2
add processed_datetime datetime null

alter table DNSRecords2
add been_processed tinyint null

alter table DNSRecords2
add titlepos integer null

-- create index after data load from p.ps1 (hybrid bcpload), and alter table commands

CREATE INDEX j1 ON DNSRecords2 (row_id); 
CREATE INDEX j2 ON DNSRecords2 (been_processed); 
CREATE INDEX j3 ON DNSRecords2 (titlepos); 
/* DROP INDEX j3 ON DNSRecords2 */

set rowcount 0
set rowcount 1000
select * from DNSRecords2 where substring(domain_name, 9, 20) = 'name:mainstreetmodels.com'
select max(row_id) from DNSRecords2
select min(row_id) from DNSRecords2
select * from DNSRecords2

update DNSRecords2 set been_processed = 1 where processed_datetime is not null
select substring('name:domainname.com', 5, 9)
select distinct recordtype from DNSRecords2
select * from DNSRecords2 where row_id = 904543794
select * from DNSRecords2 where substring(domainname = 'mainstreetmodels.com')

select * from DNSRecords2 where row_id = 1455919465
select * from DNSRecords2 where row_id = 1515119465
select * from DNSRecords2 where row_id = 110483368
select * from DNSRecords2 where row_id = 103951172
set rowcount 0
update DNSRecords2 set titlepos = charindex('<title>', homepagetext) WHERE been_processed = 1
select count(*) from DNSRecords2 where titlepos is not null
select count(*) from DNSRecords2 where titlepos is not null
select count(*) from DNSRecords2 where been_processed is not null

---select rows ---

select row_id from DNSRecords2 where been_processed is not null
select count(*) from DNSRecords2 where been_processed = 1

select charindex('<title>', homepagetext) from DNSRecords2 where row_id = 1455919465

select * from DNSRecords2 where row_id = 96882667


select * from DNSRecords2 where processed_datetime is not null
select domain_name from DNSRecords2 where substring(recordtype,9,5) = 'cname'

set rowcount 0
set rowcount 10
select datalength(homepagetext), domain_name, titlepos from DNSRecords2 where been_processed = 1  order by 1 DESC
select datalength(homepagetext), domain_name, titlepos from DNSRecords2 where datalength(homepagetext) > 1024 and datalength(homepagetext) < 10240 and been_processed = 1  order by 1 DESC

select * from DNSRecords2 where been_processed = 1  order by processed_datetime DESC
select * from DNSRecords2 where been_processed = 1  order by processed_datetime DESC
select row_id, homepagetext from DNSRecords2 where been_processed = 1 ORDER BY row_id

set rowcount 50
select row_id from DNSRecords2
select row_id, domain_name from DNSRecords2 where domain_name like '%optimize%'

/* analysis section */
select row_id, titlepos from DNSRecords2 where been_processed = 1 ORDER BY row_id
select datalength(homepagetext), domain_name, titlepos from DNSRecords2 where been_processed = 1  order by 1 DESC

set rowcount 50
go
select row_id, domain_name from DNSRecords2 where domain_name like '%microsoft%'
select row_id, domain_name from DNSRecords2 where domain_name like '%indvention%' and been_processed = 0
select * from DNSRecords2 where row_id = 87059677
go
set rowcount 0
go


/* "type":"a" */
/* "type":"cname" */

select * from DNSRecords2 where processed_datetime = null and substring(recordtype,9,5) = 'cname'
select substring(recordtype,9,5) from DNSRecords2
select * from DNSRecords2 where row_id > 500000000 and row_id < 500001000
select * from DNSRecords2 where row_id > 5000000 and row_id < 5000100

EXEC sp_rename 'dbDNS.DNSRecords2.domainname', 'domain_name', 'COLUMN';
GO


select * from DNSRecords2 where processed_datetime > dateadd(hh,-3,getdate())

select * from DNSRecords2 where been_processed = 1
select * from DNSRecords2 where been_processed is not null
select * from DNSRecords2 where been_processed is null
update DNSRecords2 set processed_datetime = getdate() where row_id > 5000000 and row_id < 5000100
update DNSRecords2 set processed_datetime = null where row_id > 5000000 and row_id < 5000100
select getdate()

/*
delete from DNSRecords2
drop table DNSRecords
*/


USE dbDNS; 
GO 
DECLARE domain_name_cursor CURSOR FOR 
SELECT domain_name FROM DNSRecords2
WHERE row_id < 1000 and row_id > 500
ORDER BY row_id; 
 
OPEN domain_name_cursor; 
 
-- Perform the first fetch. 
FETCH NEXT FROM domain_name_cursor; 
 
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. 
WHILE @@FETCH_STATUS = 0 
BEGIN 
   -- This is executed as long as the previous fetch succeeds. 
   FETCH NEXT FROM domain_name_cursor; 
END 
 
CLOSE domain_name_cursor; 
DEALLOCATE domain_name_cursor; 
GO