在ASP里面调用下面的SQL语句。SELECT a.name AS tabname , h.name AS idname FROM sys.objects AS a RIGHT JOIN sys.indexes AS h ON a.object_id = h.object_id WHERE a.type <> 's' AND h.name IS NOT null
select t1.name as TableName, t2.name as PrimaryName, t4.index_column_id as IndexColumnID, t5.name as ColumnName, t6.name as typename, t6.max_length, t6.precision, t6.scale from sys.objects t1 join sys.objects t2 on t2.parent_object_id = t1.object_id join sys.indexes t3 on t3.object_id = t2.parent_object_id and t3.name = t2.name join sys.index_columns t4 on t4.object_id = t3.object_id and t4.index_id = t3.index_id join sys.columns t5 on t5.object_id = t1.object_id and t5.column_id = t4.column_id join sys.types t6 on t5.user_type_id=t6.user_type_id where t2.type = 'pk'
您这个改成这样可以用,不然不行,但是会多出来两个不知道是什么东西的: pk_dtproperties 和 tdtproperties SELECT a.name AS tabname , h.name AS idname FROM sysobjects AS a RIGHT JOIN sysindexes AS h ON a.id = h.id WHERE a.type <> 's' AND h.name IS NOT null object_id 在表里没有啊 为什么是 sys.objects 而不是 sysobjects呢 什么原因
USE CSDN GO;WITH CTE AS ( SELECT tablename = a.name, indexname = b.NAME, columnid = D.column_id, columnname = D.name FROM sys.tables A INNER JOIN sys.indexes B ON a.[object_id] = b.[object_id] AND a.type = 'U' AND a.is_ms_shipped = 0 AND b.name IS NOT NULL INNER JOIN sys.index_columns C ON a.[object_id] = C.[object_id] AND B.index_id = C.index_id INNER JOIN sys.columns D ON a.[object_id] = D.[object_id] AND c.column_id = D.column_id ) SELECT tablename, indexname, colname = STUFF((SELECT ',' + columnname FROM cte b WHERE b.tablename=a.tablename AND b.indexname=a.indexname ORDER BY columnid FOR XML PATH('')),1,1,'') FROM CTE a --WHERE tablename = 'tab' GROUP BY tablename, indexname/* tablename indexname colname tab IX_tab_name name tab IX_tablename_fieldlist title,name,time */
select b.name 'index_name',a.name 'table_name' from sys.tables a inner join sys.indexes b on a.object_id=b.object_id
h.name AS idname
FROM sys.objects AS a
RIGHT JOIN sys.indexes AS h ON a.object_id = h.object_id
WHERE a.type <> 's' AND h.name IS NOT null
t2.name as PrimaryName,
t4.index_column_id as IndexColumnID,
t5.name as ColumnName,
t6.name as typename,
t6.max_length,
t6.precision,
t6.scale
from sys.objects t1
join sys.objects t2 on t2.parent_object_id = t1.object_id
join sys.indexes t3 on t3.object_id = t2.parent_object_id and t3.name = t2.name
join sys.index_columns t4 on t4.object_id = t3.object_id and t4.index_id = t3.index_id
join sys.columns t5 on t5.object_id = t1.object_id and t5.column_id = t4.column_id
join sys.types t6 on t5.user_type_id=t6.user_type_id
where t2.type = 'pk'
您这个改成这样可以用,不然不行,但是会多出来两个不知道是什么东西的: pk_dtproperties 和 tdtproperties SELECT a.name AS tabname , h.name AS idname FROM sysobjects AS a RIGHT JOIN sysindexes AS h ON a.id = h.id WHERE a.type <> 's' AND h.name IS NOT null
object_id 在表里没有啊 为什么是 sys.objects 而不是 sysobjects呢 什么原因
GO;WITH CTE AS
(
SELECT
tablename = a.name,
indexname = b.NAME,
columnid = D.column_id,
columnname = D.name
FROM sys.tables A
INNER JOIN sys.indexes B
ON a.[object_id] = b.[object_id]
AND a.type = 'U'
AND a.is_ms_shipped = 0
AND b.name IS NOT NULL
INNER JOIN sys.index_columns C
ON a.[object_id] = C.[object_id]
AND B.index_id = C.index_id
INNER JOIN sys.columns D
ON a.[object_id] = D.[object_id]
AND c.column_id = D.column_id
)
SELECT
tablename,
indexname,
colname = STUFF((SELECT ',' + columnname FROM cte b WHERE b.tablename=a.tablename AND b.indexname=a.indexname ORDER BY columnid FOR XML PATH('')),1,1,'')
FROM CTE a
--WHERE tablename = 'tab'
GROUP BY tablename, indexname/*
tablename indexname colname
tab IX_tab_name name
tab IX_tablename_fieldlist title,name,time
*/
select b.name 'index_name',a.name 'table_name'
from sys.tables a
inner join sys.indexes b on a.object_id=b.object_id
--sql 2000的用下面的
select b.name 'index_name',a.name 'table_name'
from sysobjects a
inner join sysindexes b on a.id=b.id
where a.type ='U'