Skip to main content
Meta Stack Exchange

Return to Answer

Post Timeline

Adding documentation about showplan that came up peripherally due to another issue: https://meta.stackexchange.com/questions/412652/
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223

  1. Due to the principle of least disruptionā„¢, as of April 8th, 2024, these views mentioned below are only exposed in 3D Printing - just happened to alphabetically be the first non-busy Data Explorer site (maybe this is a clever ploy to change that!). The rest of the sites will have them enabled on our next SEDE refresh.

  2. The views reference data that is pulled on demand from each database as it becomes available on Sunday, and then cached. It's a heavy query so, immediately after a site comes online, the information might take a couple of minutesmight take a couple of minutes to be refreshed in the views.

  3. Site names that contain ampersands come back as the "fixed" namecome back as the "fixed" name that doesn't break SEDE. For example, Anime & Amiga is presented as Anime and Amiga. For background, see this question.

  4. SEDE simply doesn't have accessdoesn't have access to some of the information folks have requested in the past, like the launch date (which you can probably approximate from MIN(Posts.CreationDate)), whether a site is in beta, and even the data dump filename. The latter is just {LOWER(site_url without https://).7z}.

  5. I'll document themthe views here, but you won't find these views you won't find these views in Data Explorer (or their definitionsmetadata/definitions) in Data Explorer. If you know why, or think you know why, please don't guess out loud - I won't comment. Let's just all agree to call it magic. šŸŽ©

  6. These views are stored in our central control database, not in the individual Q&A databases, so execution plans won't work. If you try, you'll get:

    SHOWPLAN permission denied in database 'Data.StackExchange'.

    When I made the case for making these enhancements, one of the things I committed to was not reducing security in any form. In my other answer here, I also explained an additional reason for preventing execution plans: the stored procedure uses a loop, and we don't want to be generating 360+ execution plans.

  1. Due to the principle of least disruptionā„¢, as of April 8th, 2024, these views mentioned below are only exposed in 3D Printing - just happened to alphabetically be the first non-busy Data Explorer site (maybe this is a clever ploy to change that!). The rest of the sites will have them enabled on our next SEDE refresh.

  2. The views reference data that is pulled on demand from each database as it becomes available on Sunday, and then cached. It's a heavy query so, immediately after a site comes online, the information might take a couple of minutes to be refreshed in the views.

  3. Site names that contain ampersands come back as the "fixed" name that doesn't break SEDE. For example, Anime & Amiga is presented as Anime and Amiga. For background, see this question.

  4. SEDE simply doesn't have access to some of the information folks have requested in the past, like the launch date (which you can probably approximate from MIN(Posts.CreationDate)), whether a site is in beta, and even the data dump filename. The latter is just {LOWER(site_url without https://).7z}.

  5. I'll document them here but you won't find these views (or their definitions) in Data Explorer. If you know why, or think you know why, please don't guess out loud - I won't comment. Let's just all agree to call it magic. šŸŽ©

  1. Due to the principle of least disruptionā„¢, as of April 8th, 2024, these views mentioned below are only exposed in 3D Printing - just happened to alphabetically be the first non-busy Data Explorer site (maybe this is a clever ploy to change that!). The rest of the sites will have them enabled on our next SEDE refresh.

  2. The views reference data that is pulled on demand from each database as it becomes available on Sunday, and then cached. It's a heavy query so, immediately after a site comes online, the information might take a couple of minutes to be refreshed in the views.

  3. Site names that contain ampersands come back as the "fixed" name that doesn't break SEDE. For example, Anime & Amiga is presented as Anime and Amiga. For background, see this question.

  4. SEDE simply doesn't have access to some of the information folks have requested in the past, like the launch date (which you can probably approximate from MIN(Posts.CreationDate)), whether a site is in beta, and even the data dump filename. The latter is just {LOWER(site_url without https://).7z}.

  5. I'll document the views here, but you won't find these views in Data Explorer (or their metadata/definitions). If you know why, or think you know why, please don't guess out loud - I won't comment. Let's just all agree to call it magic. šŸŽ©

  6. These views are stored in our central control database, not in the individual Q&A databases, so execution plans won't work. If you try, you'll get:

    SHOWPLAN permission denied in database 'Data.StackExchange'.

    When I made the case for making these enhancements, one of the things I committed to was not reducing security in any form. In my other answer here, I also explained an additional reason for preventing execution plans: the stored procedure uses a loop, and we don't want to be generating 360+ execution plans.

Bounty Awarded with 500 reputation awarded by GlorfindelMod
added 222 characters in body
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_url nvarchar(2048) Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time (see below).1
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.

Domains that have changed over time and have a different canonical url:

Domains that have changed over time and have a different canonical url:

Note: Url is canonical_url, not site_url.

column name data type description
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_urlnvarchar(2048)Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time (see above).
database_name nvarchar(128) name from sys.databases.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_id int Id from Sites.
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_url nvarchar(2048) Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time.1
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.

Domains that have changed over time and have a different canonical url:

column name data type description
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
database_name nvarchar(128) name from sys.databases.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_id int Id from Sites.
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_url nvarchar(2048) Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time (see below).
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.

Domains that have changed over time and have a different canonical url:

Note: Url is canonical_url, not site_url.

column name data type description
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_urlnvarchar(2048)Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time (see above).
database_name nvarchar(128) name from sys.databases.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_id int Id from Sites.
Added canonical_url column for domains that evolved.
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_urlnvarchar(2048)Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time.1
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.

Domains that have changed over time and have a different canonical url:

site_urlcanonical_url
https://avp.meta.stackexchange.comhttps://video.meta.stackexchange.com
https://avp.stackexchange.comhttps://video.stackexchange.com
https://beer.meta.stackexchange.comhttps://alcohol.meta.stackexchange.com
https://beer.stackexchange.comhttps://alcohol.stackexchange.com
https://cogsci.meta.stackexchange.comhttps://psychology.meta.stackexchange.com
https://cogsci.stackexchange.comhttps://psychology.stackexchange.com
https://health.meta.stackexchange.comhttps://medicalsciences.meta.stackexchange.com
https://health.stackexchange.comhttps://medicalsciences.stackexchange.com
https://materials.meta.stackexchange.comhttps://mattermodeling.meta.stackexchange.com
https://materials.stackexchange.comhttps://mattermodeling.stackexchange.com
https://moderators.meta.stackexchange.comhttps://communitybuilding.meta.stackexchange.com
https://moderators.stackexchange.comhttps://communitybuilding.stackexchange.com
https://writers.meta.stackexchange.comhttps://writing.meta.stackexchange.com
https://writers.stackexchange.comhttps://writing.stackexchange.com
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.
column name data type description
database_id int database_id from sys.databases.
database_name nvarchar(128) name from sys.databases.
site_name nvarchar(64) Name from Sites, e.g. Ubuntu or Webapps Meta.
tiny_name nvarchar(50) TinyName from Sites, which is also the API site parameter, e.g. askubuntu or meta.webapps.
long_name nvarchar(64) LongName from Sites, e.g. Ask Ubuntu or Web Applications Meta.
site_type nvarchar(9) For meta sites other than this one, meta_site. For this site and all main sites, main_site.
site_url nvarchar(2048) Clickable link to the site, e.g. https://askubuntu.com or https://webapps.meta.stackexchange.com.
canonical_urlnvarchar(2048)Clickable link to the site, usually identical to site_url, except for cases where domains have changed over time.1
sede_url nvarchar(2048) Clickable link to the site-specific Data Explorer. Note that a link to meta effectively lands you at its parent.
api_site_parameter nvarchar(50) The api site parameter, e.g. askubuntu or meta.webapps.
initialized datetime When we started populating that database.
made_available datetime When the database came online and was ready for queries.
processing_time time(3) The duration between initialized and made_available, in hh:mm:ss.fff format.
questions int Total number of questions in this site at the time of the current refresh.
answers int Total number of answers in this site at the time of the current refresh.
latest_post datetime The timestamp of the last post captured in this refresh.
notes varchar(46) This will be non-NULL when a database is in transition (Database is not ready.) or when the database is currently being refreshed (... refresh in progress since {hh:mm:ss} UTC ...). For the former, this should be rare and very brief. For the latter, the existing database is still available, it's just the old copy. If you want to get just online databases with the most recent week, use WHERE notes IS NOT NULL. If you want to wait until all databases are online, just check WHERE NOT EXISTS (SELECT 1 FROM dbo.sede_databases WHERE notes IS NOT NULL.

Domains that have changed over time and have a different canonical url:

site_urlcanonical_url
https://avp.meta.stackexchange.comhttps://video.meta.stackexchange.com
https://avp.stackexchange.comhttps://video.stackexchange.com
https://beer.meta.stackexchange.comhttps://alcohol.meta.stackexchange.com
https://beer.stackexchange.comhttps://alcohol.stackexchange.com
https://cogsci.meta.stackexchange.comhttps://psychology.meta.stackexchange.com
https://cogsci.stackexchange.comhttps://psychology.stackexchange.com
https://health.meta.stackexchange.comhttps://medicalsciences.meta.stackexchange.com
https://health.stackexchange.comhttps://medicalsciences.stackexchange.com
https://materials.meta.stackexchange.comhttps://mattermodeling.meta.stackexchange.com
https://materials.stackexchange.comhttps://mattermodeling.stackexchange.com
https://moderators.meta.stackexchange.comhttps://communitybuilding.meta.stackexchange.com
https://moderators.stackexchange.comhttps://communitybuilding.stackexchange.com
https://writers.meta.stackexchange.comhttps://writing.meta.stackexchange.com
https://writers.stackexchange.comhttps://writing.stackexchange.com
We no longer split up Stack Overflow that way, or publish to IA.
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
Bounty Awarded with 500 reputation awarded by CommunityBot
Added information about sede_users and marked one of the issues as resolved.
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
Added dbo.sede_sites
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
added 222 characters in body
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
added 43 characters in body
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223
Source Link
Aaron Bertrand Staff
Aaron Bertrand Staff
  • 47.2k
  • 12
  • 123
  • 223