GoGreen PC TuneUp™
Learn More

Insta-Install™
this is how we code sign our software and ssl encrypt our websites from our affiliate partner SSL.com

myBlog

myBlog Home

Back to Blog MTNCOMP


SQL Samples that come from 2019 testing for DNS management

Published: Tuesday, October 15, 2019 written by Drew Jackson
View Count: 15
Keywords: DNS, SQL, INSTR



/* 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 

 
 

if you found this article helpful, consider contributing $10, 20 or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2019 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.