{"id":885,"date":"2015-01-13T11:11:39","date_gmt":"2015-01-13T16:11:39","guid":{"rendered":"http:\/\/binaryworld.net\/blogs\/?p=885"},"modified":"2015-12-01T16:37:34","modified_gmt":"2015-12-01T21:37:34","slug":"show-8000-chars-ssms-output","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/","title":{"rendered":"How to show more than 8000 chars in SSMS output"},"content":{"rendered":"

If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx. limit) for your column value. There is no easy way in SSMS to show more than 8000 chars. Technically Grid mode allows you to do that but again it trims all new lines in your string making it useless for copy\/paste scenarios.<\/p>\n

Here is very simple trick … Basically if you convert string to XML by wrapping around CDATA tag of XML then you get clickable link as below and it preserves text formatting.<\/p>\n

\"SQL<\/a>
SQL Server SSMS output Text truncation<\/figcaption><\/figure>\n

SQL Query<\/p>\n

SELECT top 10 name, type, cast('<![CDATA[' + OBJECT_DEFINITION(x.object_id) + ']]>' as XML) as col1\r\nFROM sys.views x<\/pre>\n
This technique doesn’t face limitation of\u00a08000 characters in Text output mode\u00a0or Newline chars removal of Grid Mode.<\/p>\n
Limitations:
You may get invalid XML character error if you have some invalid XML character such as vertical tab (e.g. char(11))\u00a0<\/p>\n
Hope this helps \ud83d\ude42<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx. …<\/p>\n
  How to show more than 8000 chars in SSMS output<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":606,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,35,61],"tags":[158,85],"yoast_head":"\r\nHow to show more than 8000 chars in SSMS output - BinaryWorld Blog<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to show more than 8000 chars in SSMS output - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx. … How to show more than 8000 chars in SSMS output Read More »\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-01-13T16:11:39+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2015-12-01T21:37:34+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"150\" \/>\r\n\t<meta property=\"og:image:height\" content=\"150\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/\",\"name\":\"How to show more than 8000 chars in SSMS output - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"datePublished\":\"2015-01-13T16:11:39+00:00\",\"dateModified\":\"2015-12-01T21:37:34+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"width\":150,\"height\":150,\"caption\":\"T SQL Date format convert function\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to show more than 8000 chars in SSMS output\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to show more than 8000 chars in SSMS output - BinaryWorld Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/","og_locale":"en_US","og_type":"article","og_title":"How to show more than 8000 chars in SSMS output - BinaryWorld Blog","og_description":"If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx. … How to show more than 8000 chars in SSMS output Read More »","og_url":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/","og_site_name":"BinaryWorld Blog","article_published_time":"2015-01-13T16:11:39+00:00","article_modified_time":"2015-12-01T21:37:34+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","type":"image\/jpeg"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/","url":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/","name":"How to show more than 8000 chars in SSMS output - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","datePublished":"2015-01-13T16:11:39+00:00","dateModified":"2015-12-01T21:37:34+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","width":150,"height":150,"caption":"T SQL Date format convert function"},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/show-8000-chars-ssms-output\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"How to show more than 8000 chars in SSMS output"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/885"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=885"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/885\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/606"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}