{"id":625,"date":"2018-04-17T22:00:40","date_gmt":"2018-04-17T14:00:40","guid":{"rendered":"https:\/\/blog.freesilo.com\/?p=625"},"modified":"2018-04-17T22:04:38","modified_gmt":"2018-04-17T14:04:38","slug":"sql%e8%8e%b7%e5%8f%96%e6%95%b0%e6%8d%ae%e5%ba%93%e5%90%8d%ef%bc%8c%e8%a1%a8%e5%90%8d%ef%bc%8c%e5%88%97%e5%90%8d%ef%bc%8c%e8%af%b4%e6%98%8e%e7%ad%89%e4%bf%a1%e6%81%af","status":"publish","type":"post","link":"https:\/\/freesilo.com\/?p=625","title":{"rendered":"SQL\u83b7\u53d6\u6570\u636e\u5e93\u540d\uff0c\u8868\u540d\uff0c\u5217\u540d\uff0c\u8bf4\u660e\u7b49\u4fe1\u606f"},"content":{"rendered":"<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL\u83b7\u53d6\u6570\u636e\u5e93\u540d\uff0c\u8868\u540d\uff0c\u5217\u540d\uff0c\u8bf4\u660e\u7b49\u4fe1\u606f \u8f6c\u81ea\u6c49\u57ce\u8282\u5ea6\u4f7f http:\/\/www.cnblogs.com\/tuqun\/p\/3724000.html\r\n\/**************************************************************************************************************\r\n1\u3001\u83b7\u53d6\u6240\u6709\u7528\u6237\u540d:\r\nislogin='1'   :\u8868\u793a\u5e10\u6237\r\nislogin='0'   :\u8868\u793a\u89d2\u8272\r\nstatus='2'   :\u8868\u793a\u7528\u6237\u5e10\u6237\r\nstatus='0'   :\u8868\u793a\u7cfb\u7edf\u5e10\u6237\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM   sysusers\r\nWHERE  status = '0'\r\n       AND islogin = '1'\r\n\/**************************************************************************************************************\r\n2\u3001\u83b7\u53d6\u6240\u6709\u6570\u636e\u5e93\u540d:\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM     master..sysdatabases\r\nORDER BY name\r\n\/**************************************************************************************************************\r\n3\u3001\u83b7\u53d6\u6240\u6709\u8868\u540d:\r\nXType='U'    :\u8868\u793a\u6240\u6709\u7528\u6237\u8868;\r\nXType='S'    :\u8868\u793a\u6240\u6709\u7cfb\u7edf\u8868;\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM     \u6570\u636e\u5e93\u540d..sysobjects\r\nWHERE    xtype = 'U'\r\nORDER BY name\r\n\/**************************************************************************************************************\r\n4\u3001\u83b7\u53d6\u6240\u6709\u5b57\u6bb5\u540d:\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM   syscolumns\r\nWHERE  id = Object_id('\u8868\u540d')\r\n\/**************************************************************************************************************\r\n5\u3001\u83b7\u53d6\u6570\u636e\u5e93\u6240\u6709\u7c7b\u578b:\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM   systypes\r\n\/**************************************************************************************************************\r\n6\u3001\u83b7\u53d6\u4e3b\u952e\u5b57\u6bb5:\r\n**************************************************************************************************************\/\r\nSELECT name\r\nFROM   syscolumns\r\nWHERE  id = Object_id('\u8868\u540d')\r\n       AND colid = (SELECT TOP 1 keyno\r\n                    FROM   sysindexkeys\r\n                    WHERE  id = Object_id('\u8868\u540d'))\r\n\/**************************************************************************************************************\r\n7\u3001\u83b7\u53d6\u8868\u5b57\u6bb5\u7684\u57fa\u672c\u4fe1\u606f:\r\na.\r\n**************************************************************************************************************\/\r\nSELECT \u5b57\u6bb5\u540d = Rtrim(b.name)\r\n       ,\u4e3b\u952e = CASE \r\n               WHEN h.id IS NOT NULL THEN 'PK'\r\n               ELSE ''\r\n             END\r\n       ,\u5b57\u6bb5\u7c7b\u578b = Type_name(b.xusertype)\r\n                + CASE \r\n                    WHEN b.colstat &amp; 1 = 1 THEN '&#x5B;ID('\r\n                                                 + CONVERT(VARCHAR,Ident_seed(a.name))\r\n                                                 + ','\r\n                                                 + CONVERT(VARCHAR,Ident_incr(a.name))\r\n                                                 + ')]'\r\n                    ELSE ''\r\n                  END\r\n       ,\u957f\u5ea6 = b.length\r\n       ,\u5141\u8bb8\u7a7a = CASE b.isnullable \r\n                WHEN 0 THEN 'N'\r\n                ELSE 'Y'\r\n              END\r\n       ,\u9ed8\u8ba4\u503c = Isnull(e.TEXT,'')\r\n       ,\u5b57\u6bb5\u8bf4\u660e = Isnull(c.VALUE,'')\r\nFROM     sysobjects a\r\n         INNER JOIN  sys.all_objects aa\r\n           ON a.id=aa.object_id \r\n              AND  schema_name(schema_id)='dbo'\r\n         ,syscolumns b\r\n         LEFT OUTER JOIN sys.extended_properties c\r\n           ON b.id = c.major_id\r\n              AND b.colid = c.minor_id\r\n         LEFT OUTER JOIN syscomments e\r\n           ON b.cdefault = e.id\r\n         LEFT OUTER JOIN (SELECT g.id\r\n                                 ,g.colid\r\n                          FROM   sysindexes f\r\n                                 ,sysindexkeys g\r\n                          WHERE  (f.id = g.id)\r\n                                 AND (f.indid = g.indid)\r\n                                 AND (f.indid &gt; 0)\r\n                                 AND (f.indid &lt; 255)\r\n                                 AND (f.status &amp; 2048) &lt;&gt; 0) h\r\n           ON (b.id = h.id)\r\n              AND (b.colid = h.colid)\r\nWHERE    (a.id = b.id)\r\n         AND (a.id = Object_id('\u8868\u540d'))  --\u8981\u67e5\u8be2\u7684\u8868\u6539\u6210\u4f60\u8981\u67e5\u8be2\u8868\u7684\u540d\u79f0\r\nORDER BY b.colid\r\n\/**************************************************************************************************************\r\nb.\r\n**************************************************************************************************************\/\r\nSELECT \u8868\u540d = CASE \r\n              WHEN a.colorder = 1 THEN d.name\r\n              ELSE ''\r\n            END\r\n       ,\u8868\u8bf4\u660e = CASE \r\n                WHEN a.colorder = 1 THEN Isnull(f.VALUE,'')\r\n                ELSE ''\r\n              END\r\n       ,\u5b57\u6bb5\u5e8f\u53f7 = a.colorder\r\n       ,\u5b57\u6bb5\u540d = a.name\r\n       ,\u6807\u8bc6 = CASE \r\n               WHEN Columnproperty(a.id,a.name,'IsIdentity') = 1 THEN '\u221a'\r\n               ELSE ''\r\n             END\r\n       ,\u4e3b\u952e = CASE \r\n               WHEN EXISTS (SELECT 1\r\n                            FROM   sysobjects\r\n                            WHERE  xtype = 'PK'\r\n                                   AND name IN (SELECT name\r\n                                                FROM   sysindexes\r\n                                                WHERE  indid IN (SELECT indid\r\n                                                                 FROM   sysindexkeys\r\n                                                                 WHERE  id = a.id\r\n                                                                        AND colid = a.colid))) THEN '\u221a'\r\n               ELSE ''\r\n             END\r\n       ,\u7c7b\u578b = b.name\r\n       ,\u5b57\u6bb5\u957f\u5ea6 = a.length\r\n       ,\u5360\u7528\u5b57\u8282\u6570 = Columnproperty(a.id,a.name,'PRECISION')\r\n       ,\u5c0f\u6570\u4f4d\u6570 = Isnull(Columnproperty(a.id,a.name,'Scale'),0)\r\n       ,\u5141\u8bb8\u7a7a = CASE \r\n                WHEN a.isnullable = 1 THEN '\u221a'\r\n                ELSE ''\r\n              END\r\n       ,\u9ed8\u8ba4\u503c = Isnull(e.TEXT,'')\r\n       ,\u5b57\u6bb5\u8bf4\u660e = Isnull(g.&#x5B;value],'')\r\nFROM     syscolumns a\r\n         LEFT JOIN systypes b\r\n           ON a.xusertype = b.xusertype\r\n         INNER JOIN sysobjects d\r\n           ON (a.id = d.id)\r\n              AND (d.xtype = 'U')\r\n              AND (d.name &lt;&gt; 'dtproperties') \r\n          INNER JOIN  sys.all_objects c\r\n            ON d.id=c.object_id \r\n                AND  schema_name(schema_id)='dbo'\r\n         LEFT JOIN syscomments e\r\n           ON a.cdefault = e.id\r\n         LEFT JOIN sys.extended_properties g\r\n           ON (a.id = g.major_id)\r\n              AND (a.colid = g.minor_id)\r\n         LEFT JOIN sys.extended_properties f\r\n           ON (d.id = f.major_id)\r\n              AND (f.minor_id = 0)\r\n--where d.name='\u8981\u67e5\u8be2\u7684\u8868'         --\u5982\u679c\u53ea\u67e5\u8be2\u6307\u5b9a\u8868,\u52a0\u4e0a\u6b64\u6761\u4ef6\r\nORDER BY a.id\r\n         ,a.colorder\r\n\/***************************************************************************************************************\r\nc. \u524d\u4e24\u79cd\u4e0d\u5b8c\u6574~\r\n****************************************************************************************************************\/\r\n-- \u8cc7\u6599\u5eab\u4e2d\u6240\u6709\u8cc7\u6599\u8868\uff1a\u900f\u904e\u5167\u5b9a\u7684Stored Procedure sp_tables\r\nEXEC sp_tables @table_name = '%',@table_owner = 'dbo',@table_qualifier = @DBName; \r\n--    \u53d6\u5f97\u8cc7\u6599\u8868Schema\r\nEXEC sp_columns @TableName \r\n--    \u53d6\u5f97\u6b04\u4f4d\u8aaa\u660e\u3001\u5099\u8a3b(Extended Property)\uff1a\u9019\u500b\u662f\u900f\u904eSql\u5167\u5b9a\u7684Function\u4f86\u8655\u7406\r\nSELECT * FROM  ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default) \r\n--    \u53d6\u5f97\u4e3b\u7d22\u5f15\r\nEXEC sp_pkeys @TableName \r\n \r\n\r\n\r\n\/**************************************************************************************************************\r\n\u8868\u53ca\u5b57\u6bb5\u63cf\u8ff0\u4fe1\u606f\u5904\u7406\u793a\u4f8b\r\n**************************************************************************************************************\/\r\n\r\n--\u521b\u5efa\u8868\r\nCREATE TABLE \u8868 (\r\n  a1  VARCHAR(10)\r\n  ,a2 CHAR(2))\r\n--\u4e3a\u8868\u6dfb\u52a0\u63cf\u8ff0\u4fe1\u606f\r\nEXECUTE Sp_addextendedproperty N'MS_Description' , '\u4eba\u5458\u4fe1\u606f\u8868' , N'user' , N'dbo' , N'table' , N'\u8868' , NULL , NULL\r\nEXEC sys.sp_addextendedproperty \r\n@name=N'MS_Description', @value=N'\u4eba\u5458\u4fe1\u606f\u8868' , \r\n@level0type=N'SCHEMA',@level0name=N'dbo', \r\n@level1type=N'TABLE',@level1name=N'\u8868'\r\n \r\n\r\n\r\n--\u4e3a\u5b57\u6bb5a1\u6dfb\u52a0\u63cf\u8ff0\u4fe1\u606f\r\nEXECUTE Sp_addextendedproperty N'MS_Description' , '\u59d3\u540d' , N'user' , N'dbo' , N'table' , N'\u8868' , N'column' , N'a1'\r\nEXEC sys.sp_addextendedproperty \r\n@name=N'MS_Description', @value=N'\u4eba\u5458\u4fe1\u606f\u8868' , \r\n@level0type=N'SCHEMA',@level0name=N'dbo', \r\n@level1type=N'TABLE',@level1name=N'\u8868', \r\n@level2type=N'COLUMN',@level2name=N'a1'\r\n\r\n \r\n\r\n\r\n--\u4e3a\u5b57\u6bb5a2\u6dfb\u52a0\u63cf\u8ff0\u4fe1\u606f\r\nEXECUTE Sp_addextendedproperty N'MS_Description' , '\u6027\u522b' , N'user' , N'dbo' , N'table' , N'\u8868' , N'column' , N'a2'\r\n--\u66f4\u65b0\u8868\u4e2d\u5217a1\u7684\u63cf\u8ff0\u5c5e\u6027\uff1a\r\nEXEC Sp_updateextendedproperty 'MS_Description' , '\u5b57\u6bb51' , 'user' , dbo , 'table' , '\u8868' , 'column' , a1\r\n--\u663e\u793a\u8868\u7684\u63cf\u8ff0\u5c5e\u6027\r\nSELECT *\r\nFROM   ::fn_listextendedproperty(NULL,'user','dbo','table','\u8868','column',NULL)\r\n--\u5220\u9664\u8868\u4e2d\u5217a1\u7684\u63cf\u8ff0\u5c5e\u6027\uff1a\r\nEXEC Sp_dropextendedproperty 'MS_Description' , 'user' , dbo , 'table' , '\u8868' , 'column' , a1\r\n--\u5220\u9664\u6d4b\u8bd5\r\nDROP TABLE \u8868\r\n\r\n<\/pre>\n<p>FROM:https:\/\/www.cnblogs.com\/KingsLiu\/articles\/6046778.html<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL\u83b7\u53d6\u6570\u636e\u5e93\u540d\uff0c\u8868\u540d\uff0c\u5217\u540d\uff0c\u8bf4\u660e\u7b49\u4fe1\u606f \u8f6c\u81ea\u6c49\u57ce\u8282\u5ea6\u4f7f http:\/\/www&#8230;<br \/><a class=\"read-more-button\" href=\"https:\/\/freesilo.com\/?p=625\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-625","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/625","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=625"}],"version-history":[{"count":2,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/625\/revisions"}],"predecessor-version":[{"id":627,"href":"https:\/\/freesilo.com\/index.php?rest_route=\/wp\/v2\/posts\/625\/revisions\/627"}],"wp:attachment":[{"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=625"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=625"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/freesilo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=625"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}