{"id":362,"date":"2015-12-09T11:49:36","date_gmt":"2015-12-09T11:49:36","guid":{"rendered":"http:\/\/www.tiagoneves.net\/blog\/?p=362"},"modified":"2015-12-09T11:49:36","modified_gmt":"2015-12-09T11:49:36","slug":"procedures-nao-documentadas-sp_msforeachdb-e-sp_msforeachtable","status":"publish","type":"post","link":"https:\/\/www.tiagoneves.net\/blog\/procedures-nao-documentadas-sp_msforeachdb-e-sp_msforeachtable\/","title":{"rendered":"Procedures n\u00e3o documentadas sp_MSforeachdb e sp_MSforeachtable"},"content":{"rendered":"

Ol\u00e1 pessoal,<\/p>\n

No post de hoje vou falar sobre as procedures n\u00e3o documentadas do SQL Server sp_MSforeachdb e sp_MSforeachtable, elas s\u00e3o muito \u00fateis quando voc\u00ea se deparar com uma atividade, em que voc\u00ea necessite buscar as informa\u00e7\u00f5es em todos os bancos de dados ou todas as tabelas.\u00a0Essas procedures executam atividades em loop atrav\u00e9s dos bancos ou tabelas.<\/p>\n

Imagine que temos a seguinte situa\u00e7\u00e3o: Preciso executar o comando CHECKDB em todos os meus bancos de forma autom\u00e1tica.<\/em><\/p>\n

Poder\u00edamos fazer um script utilizando views de sistemas e montar um loop, porem podemos ficar preso a quantidade de bancos no ato de cria\u00e7\u00e3o do script, ou seja ao incluir, dropar ou at\u00e9 mesmo ao alterar o nome de um banco, voc\u00ea teria que lembrar de alterar o script.<\/p>\n

Neste caso podemos utilizar a sp_MSforeachdb como o pr\u00f3prio nome sugere, ela \u00e9 utilizada para executar determinada query para cada base atachada na inst\u00e2ncia.<\/p>\n

A sintaxe do comando sp_MSforeachdb<\/strong> \u00e9:<\/p>\n

sp_MSforeachdb @command1, @replacechar,\u00a0<\/span><\/span>
\n@command2,\u00a0<\/span><\/span>
\n@command3, @precommand, @postcommand<\/span><\/p>\n

onde:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
Parametro<\/strong><\/td>\nDescri\u00e7\u00e3o<\/strong><\/td>\nTipo<\/strong><\/td>\n<\/tr>\n
@command1<\/td>\nPrimeiro comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@replacechar<\/td>\nRepresenta o caractere que ser\u00e1 representado pelo nome do banco de dados<\/td>\nNvarchar(1)<\/td>\n<\/tr>\n
@command2<\/td>\nSegundo comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@command3<\/td>\nTerceiro comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@precommand<\/td>\nComando que ser\u00e1 executado antes dos comandos<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@postcommand<\/td>\nComando que ser\u00e1 executado ap\u00f3s os comandos enviados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Vejamos o exemplo da situa\u00e7\u00e3o acima:<\/p>\n

EXECUTE master.sys.sp_MSforeachdb'dbcc checkdb(''?'')'<\/pre>\n
\"procedure_MSforeachdb\"<\/a><\/p>\n
O comando vai executar checkdb em todos os bancos de dados da inst\u00e2ncia.<\/p>\n
\"checkdb_master\"<\/a> \"checkdb_msdb\"<\/a> \"checkdb_tempdb\"<\/a> \"checkdb_traces\"<\/a> \"checkdb_TSQL2012\"<\/a><\/p>\n
Se por ventura precisar excluir um ou mais bancos, podemos tamb\u00e9m criar filtro.<\/p>\n
EXECUTE master.sys.sp_MSforeachdb'if ''?'' NOT IN (''Master'',''MSDB'',''Model'',''Tempdb'') DBCC checkdb(''?'')'<\/pre>\n
\"checkdb_without_systemdabases\"<\/a><\/p>\n
No exemplo o checkdb foi executado em todos os bancos de usu\u00e1rios menos nas bases de sistemas.<\/p>\n
A procedure sp_MSforeachtable<\/strong> tem a mesma fun\u00e7\u00e3o, por\u00e9m ela faz um loop em todas as tabelas de um banco de dados.<\/p>\n
A sintaxe para utilizar a procedure sp_MSforeachtable<\/strong> \u00e9:<\/p>\n
sp_MSforeachdb @command1, @replacechar,\u00a0<\/span><\/span>
\n@command2,<\/span>
\n@command3, @whereand,\u00a0<\/span><\/span>
\n@precommand, @postcommand<\/span><\/p>\n
onde:<\/p>\n\n\n\n\n\n\n\n\n\n\n
Parametro<\/strong><\/td>\nDescri\u00e7\u00e3o<\/strong><\/td>\nTipo<\/strong><\/td>\n<\/tr>\n
@command1<\/td>\nPrimeiro comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@replacechar<\/td>\nRepresenta o caractere que ser\u00e1 representado pelo nome do banco de dados<\/td>\nNvarchar(1)<\/td>\n<\/tr>\n
@command2<\/td>\nSegundo comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@command3<\/td>\nTerceiro comando que ser\u00e1 executado em cada base de dados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@whereand<\/td>\nCampo para adicionar informa\u00e7\u00f5es sobre o objeto, por exemplo constraints.<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@precommand<\/td>\nComando que ser\u00e1 executado antes dos comandos<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n
@postcommand<\/td>\nComando que ser\u00e1 executado ap\u00f3s os comandos enviados<\/td>\nNvarchar(2000)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Imagine que temos a seguinte situa\u00e7\u00e3o: Preciso executar saber o tamanho de cada tabela do meu banco de dados.<\/em><\/p>\n
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''<\/pre>\n
\"procedure_MSforeachtable\"<\/a><\/p>\n
Assim como na sp_MSforeachdb tamb\u00e9m podemos filtrar as tabelas que queremos executar o comando.<\/p>\n
 EXEC sp_MSforeachtable @command1 = 'IF ''?'' NOT IN  (''[HR].[Employees]'',''[Production].[Suppliers]'') EXEC sp_spaceused ''?''';<\/pre>\n
\"checkdb_without_tables\"<\/a><\/p>\n
Al\u00e9m das procedures sp_MSforeachdb e sp_MSforeachtable existem outras procedures n\u00e3o documentadas como as da lista abaixo, n\u00e3o existe suporte ou material oficial\u00a0que ensine a utiliz\u00e1-la por parte da Microsoft.<\/p>\n
sp_checknames
\nsp_columns_rowset
\nsp_enumoledbdatasources
\nsp_fixindex
\nsp_gettypestring
\nsp_ms_marksystemobject
\nsp_msaddguidcolumn
\nsp_msaddguidindex
\nsp_msaddlogin_implicit_ntlogin
\nsp_msadduser_implicit_ntlogin
\nsp_mscheck_uid_owns_anything
\nsp_msdbuseraccess
\nsp_msdbuserpriv
\nsp_msdependencies
\nsp_msdrop_object
\nsp_msforeachdb
\nsp_msforeachtable
\nsp_msget_qualified_name
\nsp_msgettools_path
\nsp_msgetversion
\nsp_msguidtostr
\nsp_mshelpcolumns
\nsp_mshelpindex
\nsp_mshelptype
\nsp_msindexspace
\nsp_msis_pk_col
\nsp_mskilldb
\nsp_msloginmappings
\nsp_mstablekeys
\nsp_mstablerefs
\nsp_mstablespace
\nsp_msunc_to_drive
\nsp_msuniquecolname
\nsp_msuniquename
\nsp_msuniqueobjectname
\nsp_msuniquetempname
\nsp_tempdbspace
\nsp_who2
\nxp_delete_file
\nxp_dirtree
\nxp_enum_oledb_providers
\nxp_enumcodepages
\nxp_enumdsn
\nxp_enumerrorlogs
\nxp_enumgroups
\nxp_fileexist
\nxp_fixeddrives
\nxp_get_mapi_default_profile
\nxp_get_mapi_profiles
\nxp_getnetname
\nxp_qv
\nxp_readerrorlog
\nxp_regaddmultistring
\nxp_regdeletekey
\nxp_regdeletevalue
\nxp_regenumvalues
\nxp_regread
\nxp_regremovemultistring
\nxp_regwrite
\nxp_subdirs
\nxp_varbintohexstr<\/p>\n
Bom por hoje \u00e9 isso.<\/p>\n
Um abra\u00e7o,<\/p>\n
Tiago Neves<\/p>\n","protected":false},"excerpt":{"rendered":"
Ol\u00e1 pessoal, No post de hoje vou falar sobre as procedures n\u00e3o documentadas do SQL Server sp_MSforeachdb e sp_MSforeachtable, elas s\u00e3o muito \u00fateis quando voc\u00ea se deparar com uma atividade, em que voc\u00ea necessite buscar as informa\u00e7\u00f5es em todos os bancos de dados ou todas as tabelas.\u00a0Essas procedures executam atividades em loop atrav\u00e9s dos bancos…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rop_custom_images_group":[],"rop_custom_messages_group":[],"rop_publish_now":"initial","rop_publish_now_accounts":{"twitter_91251433_91251433":""},"rop_publish_now_history":[],"rop_publish_now_status":"pending","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Procedures n\u00e3o documentadas sp_MSforeachdb e sp_MSforeachtable","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_wpscppro_dont_share_socialmedia":false,"_wpscppro_custom_social_share_image":0,"_facebook_share_type":"","_twitter_share_type":"","_linkedin_share_type":"","_pinterest_share_type":"","_linkedin_share_type_page":"","_instagram_share_type":"","_medium_share_type":"","_threads_share_type":"","_google_business_share_type":"","_bluesky_share_type":"","_selected_social_profile":[],"_wpsp_enable_custom_social_template":false,"_wpsp_social_scheduling":{"enabled":false,"datetime":null,"platforms":[],"status":"template_only","dateOption":"today","timeOption":"now","customDays":"","customHours":"","customDate":"","customTime":"","schedulingType":"absolute"},"_wpsp_active_default_template":true},"categories":[2,4,20,5],"tags":[96,14],"class_list":["post-362","post","type-post","status-publish","format-standard","hentry","category-casos-do-dia-a-dia","category-scripts","category-sqlserver-geral","category-virtual-pass-br","tag-loop","tag-sqlserver"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6eIyh-5Q","jetpack-related-posts":[{"id":1845,"url":"https:\/\/www.tiagoneves.net\/blog\/conhecendo-as-server-roles-e-database-roles-do-sql-server\/","url_meta":{"origin":362,"position":0},"title":"Conhecendo as Server Roles e Database Roles do SQL Server","author":"tiagoneves","date":"14 de agosto de 2019","format":false,"excerpt":"Hoje em dia, seguran\u00e7a \u00e9 um assunto s\u00e9rio quando falamos de dados, neste post explico os n\u00edveis de acessos e permiss\u00e3o de cada role do SQL Server.","rel":"","context":"Em "Seguran\u00e7a"","block_context":{"text":"Seguran\u00e7a","link":"https:\/\/www.tiagoneves.net\/blog\/category\/seguranca\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/07\/Descricao_Acessos007.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1758,"url":"https:\/\/www.tiagoneves.net\/blog\/fim-do-suporte-sql-2008-migracao-utilizando-o-dbatools\/","url_meta":{"origin":362,"position":1},"title":"Fim do Suporte SQL 2008 – Migra\u00e7\u00e3o utilizando o DBATools","author":"tiagoneves","date":"24 de abril de 2019","format":false,"excerpt":"Neste ultimo post sobre o fim do suporte do SQL Server 2008, demonstro como efetuar uma migra\u00e7\u00e3o utilizando o pool de scripts do DBATools.","rel":"","context":"Em "Microsoft"","block_context":{"text":"Microsoft","link":"https:\/\/www.tiagoneves.net\/blog\/category\/microsoft\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/04\/Migration_SQL_Server_with_DBATools-020.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/04\/Migration_SQL_Server_with_DBATools-020.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/04\/Migration_SQL_Server_with_DBATools-020.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/04\/Migration_SQL_Server_with_DBATools-020.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1422,"url":"https:\/\/www.tiagoneves.net\/blog\/replique-os-backups-de-sua-vm-sql-server-do-azure-para-amazon\/","url_meta":{"origin":362,"position":2},"title":"Replique os backups de sua VM SQL Server do Azure para Amazon","author":"tiagoneves","date":"25 de julho de 2018","format":false,"excerpt":"Ol\u00e1 pessoal, tudo certo? No post de hoje vou compartilhar com voc\u00eas um caso que atendi em um de nossos clientes. O nosso cliente tem uma VM com o SQL Server no Azure e gostaria de ter um site de DR (disaster recovery) na Amazon. At\u00e9 ai tudo bem, voc\u00ea\u2026","rel":"","context":"Em "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.tiagoneves.net\/blog\/category\/azure\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":89,"url":"https:\/\/www.tiagoneves.net\/blog\/alterando-o-tipo-autenticacao-do-sqlserver\/","url_meta":{"origin":362,"position":3},"title":"Alterando o tipo autentica\u00e7\u00e3o do SQLServer","author":"tiagoneves","date":"10 de maio de 2015","format":false,"excerpt":"Ol\u00e1 pessoal tudo certo? Hoje vou postar sobre um erro que aconteceu comigo e perdi um tempo para encontrar o problema. O erro \u00e9 quando voc\u00ea vai logar com\u00a0usu\u00e1rio de SQL e aparece o erro \u201clogin failed for user: \u201d. Tentei de tudo para logar, troquei\u00a0a senha do usu\u00e1rio, testei\u2026","rel":"","context":"Em "Scripts"","block_context":{"text":"Scripts","link":"https:\/\/www.tiagoneves.net\/blog\/category\/scripts\/"},"img":{"alt_text":"1","src":"https:\/\/i0.wp.com\/tiagoneves.net\/blog\/wp-content\/uploads\/2015\/05\/18.jpg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/tiagoneves.net\/blog\/wp-content\/uploads\/2015\/05\/18.jpg?resize=350%2C200 1x, https:\/\/i0.wp.com\/tiagoneves.net\/blog\/wp-content\/uploads\/2015\/05\/18.jpg?resize=525%2C300 1.5x"},"classes":[]},{"id":167,"url":"https:\/\/www.tiagoneves.net\/blog\/restaurar-uma-database-sql-server-2012-em-uma-instancia-sql-server-inferior\/","url_meta":{"origin":362,"position":4},"title":"Restaurando uma database SQL Server 2012 em uma inst\u00e2ncia SQL Server inferior.","author":"tiagoneves","date":"29 de junho de 2015","format":false,"excerpt":"Ol\u00e1 pessoal, tudo certo? No post de hoje vou falar sobre uma d\u00favida que \u00e9 comum entre muitos usu\u00e1rios, algumas vezes vejo posts de d\u00favida sobre o como fazer o restore de uma base, em uma vers\u00e3o anterior da vers\u00e3o corrente do SQL Server, como por exemplo: Restaurar uma base\u2026","rel":"","context":"Em "Casos do dia-a-dia"","block_context":{"text":"Casos do dia-a-dia","link":"https:\/\/www.tiagoneves.net\/blog\/category\/casos-do-dia-a-dia\/"},"img":{"alt_text":"1.2","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/06\/1.2.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/06\/1.2.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/06\/1.2.png?resize=525%2C300 1.5x"},"classes":[]},{"id":254,"url":"https:\/\/www.tiagoneves.net\/blog\/encontrando-indices-nao-utilizados\/","url_meta":{"origin":362,"position":5},"title":"Encontrando \u00edndices n\u00e3o utilizados","author":"tiagoneves","date":"5 de agosto de 2015","format":false,"excerpt":"Ol\u00e1 pessoal, Neste post irei compartilhar um script que nos auxilia a melhorar a performance das consultas que s\u00e3o executadas em nossa inst\u00e2ncia, mostrando \u00edndices que poderiam ser criados para melhorar a performance. Demonstra\u00e7\u00e3o: CREATE TABLE t1 ( c1 INT IDENTITY, c2 AS c1 * 2, c3 AS c1 +\u2026","rel":"","context":"Em "Scripts"","block_context":{"text":"Scripts","link":"https:\/\/www.tiagoneves.net\/blog\/category\/scripts\/"},"img":{"alt_text":"missing_index","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/08\/missing_index.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/08\/missing_index.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/08\/missing_index.png?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/08\/missing_index.png?resize=700%2C400 2x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2015\/08\/missing_index.png?resize=1050%2C600 3x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/362","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/comments?post=362"}],"version-history":[{"count":5,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/362\/revisions"}],"predecessor-version":[{"id":378,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/362\/revisions\/378"}],"wp:attachment":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/media?parent=362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/categories?post=362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/tags?post=362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}