curl script to see if the dbname can be converted to a site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
result per 2015-07-28:

404
http://Meta.Mathoverflow.StackExchange.com 404 
http://Meta.Audio.StackExchange.com 404 
http://Audio.StackExchange.com 404 

301
http://Meta.Moderators.StackExchange.com 301 http://meta.communitybuilding.stackexchange.com/
http://Moderators.StackExchange.com 301 http://communitybuilding.stackexchange.com/
http://Meta.Br.StackOverflow.com 301 http://meta.pt.stackoverflow.com/
http://Br.StackOverflow.com 301 http://pt.stackoverflow.com/
http://Meta.Garage.StackExchange.com 301 http://meta.mechanics.stackexchange.com/
http://Garage.StackExchange.com 301 http://mechanics.stackexchange.com/
http://Mathoverflow.StackExchange.com 301 http://mathoverflow.net/
http://Meta.Ubuntu.StackExchange.com 301 http://meta.askubuntu.com/
http://Ubuntu.StackExchange.com 301 http://askubuntu.com/
http://Meta.Photography.StackExchange.com 301 http://meta.photo.stackexchange.com/
http://Photography.StackExchange.com 301 http://photo.stackexchange.com/

200
http://Meta.Woodworking.StackExchange.com 200 
http://Woodworking.StackExchange.com 200 
http://Meta.Musicfans.StackExchange.com 200 
http://Musicfans.StackExchange.com 200 
http://Meta.Vi.StackExchange.com 200 
http://Vi.StackExchange.com 200 
http://Meta.Coffee.StackExchange.com 200 
http://Coffee.StackExchange.com 200 
http://Meta.Engineering.StackExchange.com 200 
http://Engineering.StackExchange.com 200 
http://Meta.Lifehacks.StackExchange.com 200 
http://Lifehacks.StackExchange.com 200 
http://Meta.Ja.StackOverflow.com 200 
http://Ja.StackOverflow.com 200 
http://Meta.Economics.StackExchange.com 200 
http://Economics.StackExchange.com 200 
http://Meta.Hsm.StackExchange.com 200 
http://Hsm.StackExchange.com 200 
http://Meta.Emacs.StackExchange.com 200 
http://Emacs.StackExchange.com 200 
http://Meta.Worldbuilding.StackExchange.com 200 
http://Worldbuilding.StackExchange.com 200 
http://Meta.Startups.StackExchange.com 200 
http://Startups.StackExchange.com 200 
http://Meta.Hinduism.StackExchange.com 200 
http://Hinduism.StackExchange.com 200 
http://Meta.Buddhism.StackExchange.com 200 
http://Buddhism.StackExchange.com 200 
http://Meta.Craftcms.StackExchange.com 200 
http://Craftcms.StackExchange.com 200 
http://Meta.Puzzling.StackExchange.com 200 
http://Puzzling.StackExchange.com 200 
http://Meta.Datascience.StackExchange.com 200 
http://Datascience.StackExchange.com 200 
http://Meta.Joomla.StackExchange.com 200 
http://Joomla.StackExchange.com 200 
http://Meta.StackOverflow.com 200 
http://Meta.Earthscience.StackExchange.com 200 
http://Earthscience.StackExchange.com 200 
http://Meta.Matheducators.StackExchange.com 200 
http://Matheducators.StackExchange.com 200 
http://Meta.Expatriates.StackExchange.com 200 
http://Expatriates.StackExchange.com 200 
http://Meta.Arduino.StackExchange.com 200 
http://Arduino.StackExchange.com 200 
http://Meta.Softwarerecs.StackExchange.com 200 
http://Softwarerecs.StackExchange.com 200 
http://Meta.Beer.StackExchange.com 200 
http://Beer.StackExchange.com 200 
http://Meta.Ebooks.StackExchange.com 200 
http://Ebooks.StackExchange.com 200 
http://Meta.Aviation.StackExchange.com 200 
http://Aviation.StackExchange.com 200 
http://Meta.Italian.StackExchange.com 200 
http://Italian.StackExchange.com 200 
http://Meta.Ham.StackExchange.com 200 
http://Ham.StackExchange.com 200 
http://Meta.Pets.StackExchange.com 200 
http://Pets.StackExchange.com 200 
http://Meta.Tor.StackExchange.com 200 
http://Tor.StackExchange.com 200 
http://Meta.Astronomy.StackExchange.com 200 
http://Astronomy.StackExchange.com 200 
http://Meta.Sound.StackExchange.com 200 
http://Sound.StackExchange.com 200 
http://Meta.Space.StackExchange.com 200 
http://Space.StackExchange.com 200 
http://Meta.Blender.StackExchange.com 200 
http://Blender.StackExchange.com 200 
http://Meta.Freelancing.StackExchange.com 200 
http://Freelancing.StackExchange.com 200 
http://Meta.Opendata.StackExchange.com 200 
http://Opendata.StackExchange.com 200 
http://Meta.Networkengineering.StackExchange.com 200 
http://Networkengineering.StackExchange.com 200 
http://Meta.Reverseengineering.StackExchange.com 200 
http://Reverseengineering.StackExchange.com 200 
http://Meta.Tridion.StackExchange.com 200 
http://Tridion.StackExchange.com 200 
http://Meta.Sustainability.StackExchange.com 200 
http://Sustainability.StackExchange.com 200 
http://Meta.Ell.StackExchange.com 200 
http://Ell.StackExchange.com 200 
http://Meta.Magento.StackExchange.com 200 
http://Magento.StackExchange.com 200 
http://Meta.Anime.StackExchange.com 200 
http://Anime.StackExchange.com 200 
http://Meta.Politics.StackExchange.com 200 
http://Politics.StackExchange.com 200 
http://Meta.Expressionengine.StackExchange.com 200 
http://Expressionengine.StackExchange.com 200 
http://Meta.Robotics.StackExchange.com 200 
http://Robotics.StackExchange.com 200 
http://Meta.Genealogy.StackExchange.com 200 
http://Genealogy.StackExchange.com 200 
http://Meta.Patents.StackExchange.com 200 
http://Patents.StackExchange.com 200 
http://Meta.Salesforce.StackExchange.com 200 
http://Salesforce.StackExchange.com 200 
http://Meta.Islam.StackExchange.com 200 
http://Islam.StackExchange.com 200 
http://Meta.Russian.StackExchange.com 200 
http://Russian.StackExchange.com 200 
http://Meta.Raspberrypi.StackExchange.com 200 
http://Raspberrypi.StackExchange.com 200 
http://Meta.Chess.StackExchange.com 200 
http://Chess.StackExchange.com 200 
http://Meta.Chemistry.StackExchange.com 200 
http://Chemistry.StackExchange.com 200 
http://Meta.Windowsphone.StackExchange.com 200 
http://Windowsphone.StackExchange.com 200 
http://Meta.Workplace.StackExchange.com 200 
http://Workplace.StackExchange.com 200 
http://Meta.Cs.StackExchange.com 200 
http://Cs.StackExchange.com 200 
http://Meta.Academia.StackExchange.com 200 
http://Academia.StackExchange.com 200 
http://Meta.Sports.StackExchange.com 200 
http://Sports.StackExchange.com 200 
http://Meta.Martialarts.StackExchange.com 200 
http://Martialarts.StackExchange.com 200 
http://Meta.Outdoors.StackExchange.com 200 
http://Outdoors.StackExchange.com 200 
http://Meta.Cogsci.StackExchange.com 200 
http://Cogsci.StackExchange.com 200 
http://Meta.Poker.StackExchange.com 200 
http://Poker.StackExchange.com 200 
http://Meta.Biology.StackExchange.com 200 
http://Biology.StackExchange.com 200 
http://Meta.Chinese.StackExchange.com 200 
http://Chinese.StackExchange.com 200 
http://Meta.Movies.StackExchange.com 200 
http://Movies.StackExchange.com 200 
http://Meta.Scicomp.StackExchange.com 200 
http://Scicomp.StackExchange.com 200 
http://Meta.Spanish.StackExchange.com 200 
http://Spanish.StackExchange.com 200 
http://Meta.Bricks.StackExchange.com 200 
http://Bricks.StackExchange.com 200 
http://Meta.History.StackExchange.com 200 
http://History.StackExchange.com 200 
http://Meta.Hermeneutics.StackExchange.com 200 
http://Hermeneutics.StackExchange.com 200 
http://Meta.Linguistics.StackExchange.com 200 
http://Linguistics.StackExchange.com 200 
http://Meta.Bitcoin.StackExchange.com 200 
http://Bitcoin.StackExchange.com 200 
http://Meta.French.StackExchange.com 200 
http://French.StackExchange.com 200 
http://Meta.Dsp.StackExchange.com 200 
http://Dsp.StackExchange.com 200 
http://Meta.Crypto.StackExchange.com 200 
http://Crypto.StackExchange.com 200 
http://Meta.Productivity.StackExchange.com 200 
http://Productivity.StackExchange.com 200 
http://Meta.Gardening.StackExchange.com 200 
http://Gardening.StackExchange.com 200 
http://Meta.Philosophy.StackExchange.com 200 
http://Philosophy.StackExchange.com 200 
http://Meta.Japanese.StackExchange.com 200 
http://Japanese.StackExchange.com 200 
http://Meta.German.StackExchange.com 200 
http://German.StackExchange.com 200 
http://Meta.Sqa.StackExchange.com 200 
http://Sqa.StackExchange.com 200 
http://Meta.Music.StackExchange.com 200 
http://Music.StackExchange.com 200 
http://Meta.Parenting.StackExchange.com 200 
http://Parenting.StackExchange.com 200 
http://Meta.Fitness.StackExchange.com 200 
http://Fitness.StackExchange.com 200 
http://Meta.Pm.StackExchange.com 200 
http://Pm.StackExchange.com 200 
http://Meta.Quant.StackExchange.com 200 
http://Quant.StackExchange.com 200 
http://Meta.Codegolf.StackExchange.com 200 
http://Codegolf.StackExchange.com 200 
http://Meta.Codereview.StackExchange.com 200 
http://Codereview.StackExchange.com 200 
http://Meta.Graphicdesign.StackExchange.com 200 
http://Graphicdesign.StackExchange.com 200 
http://Meta.Writers.StackExchange.com 200 
http://Writers.StackExchange.com 200 
http://Meta.Homebrew.StackExchange.com 200 
http://Homebrew.StackExchange.com 200 
http://Meta.Boardgames.StackExchange.com 200 
http://Boardgames.StackExchange.com 200 
http://Meta.Money.StackExchange.com 200 
http://Money.StackExchange.com 200 
http://Meta.Christianity.StackExchange.com 200 
http://Christianity.StackExchange.com 200 
http://Meta.Travel.StackExchange.com 200 
http://Travel.StackExchange.com 200 
http://Meta.Mathematica.StackExchange.com 200 
http://Mathematica.StackExchange.com 200 
http://Meta.Rpg.StackExchange.com 200 
http://Rpg.StackExchange.com 200 
http://Meta.Judaism.StackExchange.com 200 
http://Judaism.StackExchange.com 200 
http://Meta.Skeptics.StackExchange.com 200 
http://Skeptics.StackExchange.com 200 
http://Meta.Scifi.StackExchange.com 200 
http://Scifi.StackExchange.com 200 
http://Meta.Ux.StackExchange.com 200 
http://Ux.StackExchange.com 200 
http://Meta.Diy.StackExchange.com 200 
http://Diy.StackExchange.com 200 
http://Meta.Bicycles.StackExchange.com 200 
http://Bicycles.StackExchange.com 200 
http://Meta.Sharepoint.StackExchange.com 200 
http://Sharepoint.StackExchange.com 200 
http://Meta.Dba.StackExchange.com 200 
http://Dba.StackExchange.com 200 
http://Meta.Drupal.StackExchange.com 200 
http://Drupal.StackExchange.com 200 
http://Meta.Security.StackExchange.com 200 
http://Security.StackExchange.com 200 
http://Meta.Physics.StackExchange.com 200 
http://Physics.StackExchange.com 200 
http://Meta.Android.StackExchange.com 200 
http://Android.StackExchange.com 200 
http://Meta.Wordpress.StackExchange.com 200 
http://Wordpress.StackExchange.com 200 
http://Meta.Electronics.StackExchange.com 200 
http://Electronics.StackExchange.com 200 
http://Meta.Gis.StackExchange.com 200 
http://Gis.StackExchange.com 200 
http://Meta.Apple.StackExchange.com 200 
http://Apple.StackExchange.com 200 
http://Meta.Cooking.StackExchange.com 200 
http://Cooking.StackExchange.com 200 
http://Meta.Unix.StackExchange.com 200 
http://Unix.StackExchange.com 200 
http://Meta.Math.StackExchange.com 200 
http://Math.StackExchange.com 200 
http://Meta.Cstheory.StackExchange.com 200 
http://Cstheory.StackExchange.com 200 
http://Meta.Tex.StackExchange.com 200 
http://Tex.StackExchange.com 200 
http://Meta.English.StackExchange.com 200 
http://English.StackExchange.com 200 
http://Meta.Programmers.StackExchange.com 200 
http://Programmers.StackExchange.com 200 
http://Meta.Gaming.StackExchange.com 200 
http://Gaming.StackExchange.com 200 
http://Meta.Webmasters.StackExchange.com 200 
http://Webmasters.StackExchange.com 200 
http://Meta.GameDev.StackExchange.com 200 
http://GameDev.StackExchange.com 200 
http://Meta.WebApps.StackExchange.com 200 
http://StackApps.com 200 
http://Meta.Stats.StackExchange.com 200 
http://Stats.StackExchange.com 200 
http://WebApps.StackExchange.com 200 
http://Meta.ServerFault.com 200 
http://Meta.SuperUser.com 200 
http://Meta.StackExchange.com 200 
http://ServerFault.com 200 
http://SuperUser.com 200 
http://StackOverflow.com 200 
http://Meta.Elementaryos.StackExchange.com 200 
http://Elementaryos.StackExchange.com 200 
http://Meta.Law.StackExchange.com 200 
http://Mythology.StackExchange.com 200 
http://Meta.Rus.StackExchange.com 200 
http://Rus.StackExchange.com 200 
http://Meta.Health.StackExchange.com 200 
http://Health.StackExchange.com 200 
http://Meta.Civicrm.StackExchange.com 200 
http://Ru.StackOverflow.com 200 
http://Civicrm.StackExchange.com 200 
http://Meta.Ru.StackOverflow.com 200 
http://Law.StackExchange.com 200 
http://Meta.Mythology.StackExchange.com 200 
http://Meta.Opensource.StackExchange.com 200 
http://Opensource.StackExchange.com 200 

*/

-- start create url from dbname
IF OBJECT_ID ( '#fixupsiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      -- we have three sites that have borked DNS entries
      set @dbname = (case @dbname
      when 'StackExchange.Audio' then 'StackExchange.Avp'
      when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
      when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
      else @dbname
      end)
      -- and one of those doesn't want to end with .com
      if @dbname <> 'net.Mathoverflow.meta' 
         set @dbname = 'com.' + @dbname
      exec #recursesiteurl @dbname,  @res OUTPUT
   end
GO

-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #recursesiteurl;
GO

create procedure #recursesiteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
       
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #recursesiteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string
declare @url nvarchar(250)

-- result table
create table #all_posts ( site nvarchar(250));
                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
   exec #siteurl @db_c_name, @url output
   insert into #all_posts values (@url)
   
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

/*
-- compensate
if we have .net don't do .com
*/
select site
, 'http://' + site 
, 'ping 1.1.1.1 -n 1 -w 3000 > nul & curl  -I -w "%%{url_effective}%%{http_code} %%{redirect_url}\n" -o dummy --url http://' + site  as curl
from #all_posts
where site like '%avp%'or site like '%math%'

drop procedure #recursesiteurl
drop procedure #siteurl

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results