{"id":1763,"date":"2020-01-21T17:40:23","date_gmt":"2020-01-22T00:40:23","guid":{"rendered":"http:\/\/jim-zimmerman.com\/?p=1763"},"modified":"2020-01-31T16:58:15","modified_gmt":"2020-01-31T23:58:15","slug":"mssql-notes","status":"publish","type":"post","link":"https:\/\/jim-zimmerman.com\/?p=1763","title":{"rendered":"MSSQL Notes"},"content":{"rendered":"<p>To determine a view definition:<\/p>\n<blockquote><p>SELECT DEFINITION FROM sys.sql_modules WHERE object_id = OBJECT_ID(&#8216;<em>viewschema<\/em>.<em>viewname<\/em>&#8216;)<\/p><\/blockquote>\n<p>To list all the tables in a database:<\/p>\n<blockquote><p>SELECT * FROM INFORMATION_SCHEMA.TABLES<\/p><\/blockquote>\n<p>To list all the columns and tables in a database:<\/p>\n<blockquote><p>SELECT * FROM INFORMATION_SCHEMA.COLUMNS<\/p><\/blockquote>\n<p>OUTER JOIN example:<\/p>\n<blockquote><p>SELECT xx.column1, yy.column2<br \/>\nFROM table1 xx<br \/>\nLEFT OUTER JOIN DB2.schema1.table2 yy ON xx.column1 = yy.column2<\/p><\/blockquote>\n<p>Change the time format:<br \/>\nSome CONVERT statements to the time format from DATETIME to something else:<\/p>\n<blockquote><p>DECLARE @SOMETIME DATETIME<br \/>\nSET @SOMETIME = &#8216;2020-01-31 11:00:00 AM&#8217;<br \/>\nSELECT CONVERT(VARCHAR(20), @SOMETIME, 22)<br \/>\n01\/31\/20 11:00:00 AM<\/p><\/blockquote>\n<p>This one has the four digit year:<\/p>\n<blockquote><p>DECLARE @SOMETIME DATETIME<br \/>\nSET @SOMETIME = &#8216;2020-01-31 11:00:00 AM&#8217;<br \/>\nSELECT CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 101) + &#8216; &#8216; + CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 8)<br \/>\n01\/31\/2020 11:00:00<\/p><\/blockquote>\n<p>Selecting records based on time example:<br \/>\nThis will returns records (in this case just a bunch of time variables), if the the @SOMEDATETIME variable is between now and one hour ago:<\/p>\n<blockquote><p>DECLARE @SOMEDATETIME DATETIME<br \/>\nDECLARE @CURRENTDATETIME DATETIME<br \/>\nDECLARE @CURRENTDATETIMEMINUS DATETIME<br \/>\nSET @SOMEDATETIME = &#8216;2020-01-31 15:30:00.000&#8217;<br \/>\nSET @CURRENTDATETIME = CONVERT(VARCHAR(20), GETDATE(), 101) + &#8216; &#8216; + CONVERT(VARCHAR(20), GETDATE(), 8)<br \/>\nSET @CURRENTDATETIMEMINUS = CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 101) + &#8216; &#8216; + CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 8)<\/p>\n<p>SELECT @SOMEDATETIME AS SOMEDATETIME<br \/>\n, @CURRENTDATETIMEMINUS AS CURRENTDATETIMEMINUS<br \/>\n, @CURRENTDATETIME AS CURRENTDATETIME<br \/>\nWHERE @SOMEDATETIME<br \/>\nBETWEEN @CURRENTDATETIMEMINUS AND @CURRENTDATETIME<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>To determine a view definition: SELECT DEFINITION FROM sys.sql_modules WHERE object_id = OBJECT_ID(&#8216;viewschema.viewname&#8216;) To list all the tables in a database: SELECT * FROM INFORMATION_SCHEMA.TABLES To list all the columns and tables in a database: SELECT * FROM INFORMATION_SCHEMA.COLUMNS OUTER JOIN example: SELECT xx.column1, yy.column2 FROM table1 xx LEFT OUTER JOIN DB2.schema1.table2 yy ON xx.column1 [&#038;hellip<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1763","post","type-post","status-publish","format-standard","hentry","category-documentation"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1763"}],"version-history":[{"count":7,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1763\/revisions"}],"predecessor-version":[{"id":1777,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1763\/revisions\/1777"}],"wp:attachment":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}