select * from sys.tables select * from sys.columns select * from sys.all_objects这三表联合查询就可以了
SELECT C.column_id ,C.name ,type = T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')' -- time WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetime2 WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetimeoffset WHEN 106 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- decimal WHEN 108 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- numeric WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varbinary WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varchar WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- binary WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- char WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nvarchar WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nchar ELSE '' END ,C.is_identity ,C.is_nullable ,is_key =CASE WHEN I.column_id IS NULL THEN 0 ELSE 1 END ,DefaultText = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'') FROM sys.columns C INNER JOIN sys.types T ON C.user_type_id = T.user_type_id LEFT JOIN sys.index_columns I ON I.[object_id] = C.[object_id] AND I.index_id <=2 AND I.column_id =C.column_id LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id WHERE C.[object_id] = object_id('tablename');
select * from tablexxx 这种语句,明显是全表扫描速度最快,根本不需要走索引,但是msssql的做法明显是走了主码这个索引。这只是一种基于规则的优化方式,是相当过时和落伍的,现在数据库普遍采用基于代价的优化方式,依靠实际情况来统计执行计划的性能,而不是规则。
select * from sys.columns
select * from sys.all_objects这三表联合查询就可以了
C.column_id
,C.name
,type = T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')' -- time
WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetime2
WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetimeoffset
WHEN 106 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- decimal
WHEN 108 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- numeric
WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varbinary
WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varchar
WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- binary
WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- char
WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nvarchar
WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nchar
ELSE ''
END
,C.is_identity
,C.is_nullable
,is_key =CASE WHEN I.column_id IS NULL THEN 0 ELSE 1 END
,DefaultText = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')
FROM sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
LEFT JOIN sys.index_columns I ON I.[object_id] = C.[object_id]
AND I.index_id <=2 AND I.column_id =C.column_id
LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id
AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id
WHERE C.[object_id] = object_id('tablename');
首先,mssql默认把表存放聚集索引里,本身就效率低,谁告诉你我想按照顺序排放了?自以为是的代替别人做事,这不是多次一举吗?而且聚集索引在插入修改数据时,如果数据量大了,有严重的效率问题。
mssql中的聚集索引可能等同于 oracle中的“索引排序表”,指的是表中的数据是按照某个索引的书序存放的。但是在oracle中索引排序表应用很少,因为没必要。