{"id":570,"date":"2019-03-12T22:18:02","date_gmt":"2019-03-12T22:18:02","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=570"},"modified":"2022-12-25T10:13:04","modified_gmt":"2022-12-25T10:13:04","slug":"t-sql-tuesday-112-dipping-into-your-cookie-jar","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2019\/03\/12\/t-sql-tuesday-112-dipping-into-your-cookie-jar\/","title":{"rendered":"T-SQL Tuesday #112- Buscando en el tarro de las galletas"},"content":{"rendered":"<p>In this post I want to share those scripts that I use probably every day&nbsp;<a href=\"https:\/\/nocolumnname.blog\/2019\/03\/05\/t-sql-tuesday-112-dipping-into-your-cookie-jar\/\" target=\"blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/07\/tsql2sday150x150.jpg\" alt=\"tsql2sday150x150\" width=\"154\" height=\"154\" style=\"float:left;margin-right:7px\"><\/a><\/p>\n<p>&nbsp;This post is part of the <a href=\"http:\/\/tsqltuesday.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">T-SQL Tuesday<\/a>, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server. <a href=\"https:\/\/nocolumnname.blog\/2019\/03\/05\/t-sql-tuesday-112-dipping-into-your-cookie-jar\/\" target=\"_blank\" rel=\"noopener noreferrer\"><\/a><br \/>\nThis month&#8217;s host is my good friend Shane O&#8217;Neill (<a href=\"https:\/\/nocolumnname.blog\/\" target=\"_blank\" rel=\"noopener noreferrer\">B<\/a>|<a href=\"https:\/\/twitter.com\/SOZDBA\" target=\"_blank\" rel=\"noopener noreferrer\">T<\/a>) and he wants us to share some cookies with all you guys.<\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n<strong>Background<\/strong><\/p>\n<p>Some time ago, when I started this blog, my idea was to learn more about SQL Server by experimenting and writing about it.<br \/>\nBut obviously I don&#8217;t pay my bacon just playing around with SQL, I have to do actual work to do get money at the end of each month \ud83d\ude42<\/p>\n<p>To save me some time on my day-to-day tasks, I filled little by little a good jar of cookies, which was good fun and tought me plenty of things that now I know.<\/p>\n<p>And because the SQL Server community is like that, I&#8217;m not going to give you one, but the whole jar.<\/p>\n<p>&nbsp;<br \/>\n<strong>Here is my jar<\/strong><\/p>\n<p>Last summer I decided to start with <a href=\"https:\/\/github.com\/SQLDoubleG\/DBA-database\" target=\"_blank\" rel=\"noopener noreferrer\">my own repository on Github<\/a><\/p>\n<p>It&#8217;s not very well documented as a whole, but each script is, and the names are very descriptive.<\/p>\n<p>If you are afraid or just cannot create a whole database, you still can benefit from the it, because in the <a href=\"https:\/\/github.com\/SQLDoubleG\/DBA-database\/tree\/master\/Scripts\" target=\"_blank\" rel=\"noopener noreferrer\">Scripts folder<\/a>, there is a variety of them which are stand-alone and can run independently on SQL Servers from 2008 onwards.<\/p>\n<p>The list is as follows<\/p>\n<ul>\n<li><strong>BackupSizeInfo.sql<\/strong>, to display backup infomation, can be filtered by database, backup type and provides a basic RESTORE DATABASE command which can be really useful for this long restore sequences as it can return the N last backups in ascending or descending order<\/li>\n<li><strong>DatabaseFilesInfo.sql<\/strong>, returns information about database files, including size, used space, disk space and more. Can be filtered by database or file type, for example LOG files.<\/li>\n<li><strong>DatabaseSizeInformation.sql<\/strong>, database size split in DATA, LOG, FILESTREAM, USED and TOTAL size<\/li>\n<li><strong>DatabaseSnapshotCreate.sql<\/strong>, will script out or create a database snapshot, specially useful when there are multiple database files.<\/li>\n<li><strong>IndexDescription.sql<\/strong>, comprehensive list of indexes with sizes and usage stats. Also will return DROP and CREATE statements.<\/li>\n<li><strong>IndexMissing.sql<\/strong>, based on missing index recommendations would return stats and CREATE index statement<\/li>\n<li><strong>JobsHistory.sql<\/strong>to list jobs along requested historical executions, can be filtered by the name and steps command<\/li>\n<li><strong>ListAllProgrammability.sql<\/strong>, to return stored procedures, functions, views and trigges, along create statements<\/li>\n<li><strong>RunningQueries.sql<\/strong>, lightwight tool to see queries currently running on the server, can be filtered by SPID tweaked to show only blocked\/blocking sessions<\/li>\n<li><strong>SecurityAuditDatabaseUsers.sql<\/strong>, database users and permissions<\/li>\n<li><strong>SecurityAuditServerLogins.sql<\/strong>, servers principals and permissions, see this <a href=\"https:\/\/sqldoubleg.live-website.com\/2017\/03\/14\/t-sql-tuesday-88-the-security-audit-wtf\/\" target=\"_blank\" rel=\"noopener noreferrer\">post<\/a><\/li>\n<li><strong>StrSQLSearch.sql<\/strong>, to find any pattern anywhere, tables, columns, sql modules, jobs, really love this one<\/li>\n<li><strong>TableDescription.sql<\/strong>, great to identify HEAPs, big tables, and more. See previous <a href=\"https:\/\/sqldoubleg.live-website.com\/2018\/07\/10\/t-sql-tuesday-104-finding-tables-and-columns\/\" target=\"_blank\" rel=\"noopener noreferrer\">post<\/a><\/li>\n<\/ul>\n<p>&nbsp;<br \/>\n<strong>Conclusion<\/strong><\/p>\n<p>Hopefully you found these scripts useful and just to say thanks again to Shane for hosting this month&#8217;s party.<\/p>\n<p>Thanks for reading!<\/p>\n<p>&nbsp;<\/p>","protected":false},"excerpt":{"rendered":"<p>In this post I want to share those scripts that I use probably every day&nbsp; &nbsp;This post is part of the T-SQL Tuesday, which is a monthly blog party on the second&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19,43],"tags":[15,60],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/570"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=570"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/570\/revisions"}],"predecessor-version":[{"id":574,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/570\/revisions\/574"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}