{"id":932,"date":"2022-10-26T14:55:29","date_gmt":"2022-10-26T06:55:29","guid":{"rendered":"https:\/\/www.dd-home.top\/?p=932"},"modified":"2022-10-26T14:55:29","modified_gmt":"2022-10-26T06:55:29","slug":"sqlserver-%e6%9f%a5%e8%af%a2%e8%a1%a8%e6%a0%bc%e7%9a%84%e6%95%b0%e6%8d%ae%e5%b1%9e%e6%80%a7","status":"publish","type":"post","link":"https:\/\/www.dd-home.top\/?p=932","title":{"rendered":"SqlServer \u67e5\u8be2\u8868\u683c\u7684\u6570\u636e\u5c5e\u6027"},"content":{"rendered":"
USE \u5e93\u540d;<\/span>\r\n \r\n \r\nSELECT<\/span><\/span>\r\n \u00a0 \u00a0 \u8868\u540d \u00a0 \u00a0 \u00a0 = Case When A.colorder<\/span>=1<\/span> Then D.name<\/span> Else ''<\/span> End,<\/span>\r\n \u00a0 \u00a0 \u8868\u8bf4\u660e \u00a0 \u00a0 = Case When A.colorder<\/span>=1<\/span> Then isnull(F.value<\/span>,''<\/span>) Else ''<\/span> End,<\/span>\r\n \u00a0 \u00a0 \u5b57\u6bb5\u5e8f\u53f7 \u00a0 = A.colorder<\/span>,<\/span>\r\n \u00a0 \u00a0 \u5b57\u6bb5\u540d \u00a0 \u00a0 = A.name<\/span>,<\/span>\r\n \u00a0 \u00a0 \u5b57\u6bb5\u8bf4\u660e \u00a0 = isnull(G.[value],''<\/span>),<\/span>\r\n \u00a0 \u00a0 \u6807\u8bc6 \u00a0 \u00a0 \u00a0 = Case When COLUMNPROPERTY( A.id<\/span>,A.name<\/span>,'IsIdentity'<\/span>)=1<\/span> Then '\u221a'<\/span>Else ''<\/span> End,<\/span>\r\n \u00a0 \u00a0 \u4e3b\u952e \u00a0 \u00a0 \u00a0 = Case When exists(SELECT<\/span> 1<\/span> FROM<\/span> sysobjects Where<\/span> xtype='PK'<\/span> and<\/span> parent_obj=A.id<\/span> and<\/span> name in<\/span> (<\/span>\r\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0SELECT<\/span> name FROM<\/span> sysindexes WHERE<\/span> indid in<\/span>( SELECT<\/span> indid FROM<\/span> sysindexkeys WHERE<\/span> id = A.id<\/span> AND<\/span> colid=A.colid<\/span>))) then '\u221a'<\/span> else ''<\/span> end,<\/span>\r\n \u00a0 \u00a0 \u7c7b\u578b \u00a0 \u00a0 \u00a0 = B.name<\/span>,<\/span>\r\n \u00a0 \u00a0 \u5360\u7528\u5b57\u8282\u6570 = A.Length<\/span>,<\/span>\r\n \u00a0 \u00a0 \u957f\u5ea6 \u00a0 \u00a0 \u00a0 = COLUMNPROPERTY(A.id<\/span>,A.name<\/span>,'PRECISION'<\/span>),<\/span>\r\n \u00a0 \u00a0 \u5c0f\u6570\u4f4d\u6570 \u00a0 = isnull(COLUMNPROPERTY(A.id<\/span>,A.name<\/span>,'Scale'<\/span>),0<\/span>),<\/span>\r\n \u00a0 \u00a0 \u5141\u8bb8\u7a7a \u00a0 \u00a0 = Case When A.isnullable<\/span>=1<\/span> Then '\u221a'<\/span>Else ''<\/span> End,<\/span>\r\n \u00a0 \u00a0 \u9ed8\u8ba4\u503c \u00a0 \u00a0 = isnull(E.Text<\/span>,''<\/span>)<\/span>\r\n FROM<\/span><\/span>\r\n \u00a0 \u00a0 syscolumns A<\/span>\r\n Left Join<\/span><\/span>\r\n \u00a0 \u00a0 systypes B<\/span>\r\n On<\/span><\/span>\r\n \u00a0 \u00a0 A.xusertype<\/span>=B.xusertype<\/span><\/span>\r\n Inner Join<\/span><\/span>\r\n \u00a0 \u00a0 sysobjects D<\/span>\r\n On<\/span><\/span>\r\n \u00a0 \u00a0 A.id<\/span>=D.id<\/span> \u00a0and<\/span> D.xtype<\/span>='U'<\/span> and<\/span>  D.name<\/span><>'dtproperties'<\/span><\/span>\r\n Left Join<\/span><\/span>\r\n \u00a0 \u00a0 syscomments E<\/span>\r\n on<\/span><\/span>\r\n \u00a0 \u00a0 A.cdefault<\/span>=E.id<\/span><\/span>\r\n Left Join<\/span><\/span>\r\n sys.extended_properties<\/span>  G<\/span>\r\n on<\/span><\/span>\r\n \u00a0 \u00a0 A.id<\/span>=G.major_id<\/span> and<\/span> A.colid<\/span>=G.minor_id<\/span><\/span>\r\n Left Join<\/span><\/span>\r\n \r\n sys.extended_properties<\/span> F<\/span>\r\n On<\/span><\/span>\r\n \u00a0 \u00a0 D.id<\/span>=F.major_id<\/span> and<\/span> F.minor_id<\/span>=0<\/span><\/span>\r\n \u00a0 \u00a0 where<\/span> d.name<\/span>='table_name'<\/span> \u00a0 \u00a0--\u5982\u679c\u53ea\u67e5\u8be2\u6307\u5b9a\u8868,\u52a0\u4e0a\u6b64\u6761\u4ef6<\/span><\/span>\r\n Order<\/span> By<\/span><\/span>\r\n \u00a0 \u00a0 A.id<\/span>,A.colorder<\/span><\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"
USE \u5e93\u540d; SELECT \u00a0 \u00a0 \u8868\u540d \u00a0 \u00a0 \u00a0 = Case When A.colorder=1 Th … \u7ee7\u7eed\u9605\u8bfbSqlServer \u67e5\u8be2\u8868\u683c\u7684\u6570\u636e\u5c5e\u6027<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[73],"class_list":["post-932","post","type-post","status-publish","format-standard","hentry","category-sql","tag-sqlserver"],"_links":{"self":[{"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/posts\/932","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=932"}],"version-history":[{"count":1,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/posts\/932\/revisions"}],"predecessor-version":[{"id":933,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=\/wp\/v2\/posts\/932\/revisions\/933"}],"wp:attachment":[{"href":"https:\/\/www.dd-home.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=932"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dd-home.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}