Holistic Systems

The following SQL script demonstrates how to perform multiple reverse DNS lookups from MS SQL Server.

The script uses the xp_cmdshell stored procedure to execute the nslookup DOS command. The output from this command is then imported into a database table.

You will need to enable xp_cmdshell in the “Surface Area Configuration Tool” on your SQL Server. This does have security implications when running in a high security production environment, so you may prefer to run this script in a non-production environment.

This particular script updates a table called ip_address_data for entries where the column arp_domain is currently null. The script also logs a date when the reverse DNS lookup was done so that you can periodically refresh the domains.

Minor caution: it takes a relatively long time in database query terms (about 1 or 2 seconds) to perform each reverse DNS lookup, you need to factor this is into any processing you do with this script.

declare @ip_address varchar(16)
declare @cmd varchar(100)

declare curs cursor LOCAL READ_ONLY
for select ip_address from ip_address_data where arp_domain is null
open curs

create table #output(line nvarchar(100))

while 1=1
begin
fetch next from curs into @ip_address
if @@fetch_status != 0 break

set @cmd = ‘nslookup ‘+@ip_address

insert into #output
execute xp_cmdshell @cmd

update ip_address_data set
arp_domain = (select substring(line,10,99) from #output where line like ‘Name%’),
last_cached = getdate()
where ip_address = @ip_address

delete #output

end

drop table #output
close curs
deallocate curs

The following SQL script will delete all tables from a Microsoft SQL Server Database.

After execution of this script, all of your data (and data tables) will be gone. Appropriate care should be exercised.

The current version of this script will not remove any tables that are referenced by foreign keys. You must remove any foreign keys by hand before running the script.

If you’re not sure, just run the script and then examine any remaining tables and remove any foreign keys. Then run the script again.

DECLARE tables_cursor CURSOR
FOR SELECT name FROM sysobjects WHERE type = ‘U’

OPEN tables_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC (‘DROP TABLE ‘ + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END

DEALLOCATE tables_cursor

PRINT ‘All user-defined tables have been dropped from the database.’