根据sqlservice自动生产代码核心语句

最新推荐文章于 2022-01-17 13:18:12 发布

原创最新推荐文章于 2022-01-17 13:18:12 发布 · 179 阅读

0 ·

CC 4.0 BY-SA版权

SqlServer 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询语句，用于从SQL Server数据库中获取指定表的所有字段详细信息，包括字段名、数据类型、长度、默认值、是否允许为空以及是否为主键。此查询还涉及多个表的连接，展示了如何使用SQL进行高级数据检索。

SELECT  Sysobjects.name AS TABLE_NAME ,
								syscolumns.Id  AS TABLE_ID,
								syscolumns.name AS COLUMN_NAME ,
								systypes.name AS DATA_TYPE ,
								syscolumns.length AS CHARACTER_MAXIMUM_LENGTH ,
								sys.extended_properties.[value] AS COLUMN_DESCRIPTION ,
								syscomments.text AS COLUMN_DEFAULT ,
								syscolumns.isnullable AS IS_NULLABLE,
                                (case when exists(SELECT 1 FROM sysobjects where xtype= 'PK' and name in ( 
                                SELECT name FROM sysindexes WHERE indid in( 
                                SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid=syscolumns.colid 
                                ))) then 1 else 0 end) as IS_PRIMARYKEY

								FROM    syscolumns
								INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
								LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
								LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid
																			 AND sys.extended_properties.major_id = syscolumns.id
																		   )
								LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
								WHERE   syscolumns.id IN ( SELECT   id
												   FROM     SYSOBJECTS
												   WHERE    xtype in( 'U','V') )
								AND ( systypes.name <> 'sysname' ) AND Sysobjects.name='tablename'  AND systypes.name<>'geometry' AND systypes.name<>'geography'  ORDER BY syscolumns.colid

来自sqlsugar的源码，大佬就是大佬，码怪！