Get Indexes Fields
-
Hi ...
How to get indexes fields from table using store porcedure ?
thanks,
Hendry
-
sp_helpindex [ @objname = ] 'name'
for local tables, and the following for remote linked server tables is helpfull
sp_indexes [ @table_server = ] 'table_server'
[ , [@table_name = ] 'table_name' ]
[ , [ @table_schema = ] 'table_schema' ]
[ , [ @table_catalog = ] 'table_db' ]
[ , [ @index_name = ] 'index_name' ]
[ , [ @is_unique = ] 'is_unique' ]
See BOL for more detail.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 04/22/2002 7:47:27 PM
-
Hi ...
I mean i have this SP ... (Got From pkarbowiak) and modified it to get the indexes field .. but cannot work properly
CREATE PROC mysp_columns (@tableName varchar(50))
AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[dbo].['+@tableName+']') AND OBJECTPROPERTY(id, N'IsUserTable') =1)
BEGIN
SELECT
a.name Name,
b.name DataType,
CASE b.name
WHEN 'nchar' THEN a.length/2
WHEN 'nvarchar' THEN a.length/2
ELSE a.length
END [Size],
CASE b.name
WHEN 'bit' THEN 'Boolean'
WHEN 'char' THEN 'String'
WHEN 'datetime' THEN 'Date'
WHEN 'decimal' THEN 'String'
WHEN 'float' THEN 'Double'
WHEN 'int' THEN 'Long'
WHEN 'money' THEN 'Currency'
WHEN 'nchar' THEN 'String'
WHEN 'ntext' THEN 'String'
WHEN 'numeric' THEN 'String'
WHEN 'nvarchar' THEN 'String'
WHEN 'real' THEN 'Single'
WHEN 'smalldatetime' THEN 'Date'
WHEN 'smallint' THEN 'Integer'
WHEN 'smallmoney' THEN 'Currency'
WHEN 'text' THEN 'String'
WHEN 'tinyint' THEN 'Byte'
WHEN 'varchar' THEN 'String'
ELSE 'Variant'
END VBType,
isnullable NoNull,
a.status/128 IsIdentity,
(SELECT 1 FROM sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE c_obj.uid = user_id()
AND c_obj.xtype = 'PK'
AND t_obj.id = c_obj.parent_obj
AND t_obj.xtype = 'U'
AND t_obj.id = col.id
AND col.name = index_col(t_obj.name, i.indid,v.number)
AND t_obj.id = i.id
AND c_obj.name = i.name
AND v.number > 0
AND v.number <= i.keycnt
AND v.type = 'P'
AND t_obj.name = @tableName
AND col.name = a.name
) IsPK,
(SELECT TOP 1 object_name(ref.rkeyid) FROM sysobjects c_obj,
sysobjects t_obj, syscolumns col, sysreferences ref
WHERE c_obj.uid = user_id()
AND c_obj.xtype IN ('F ')
AND t_obj.id = c_obj.parent_obj
AND t_obj.id = col.id
AND col.colid IN (ref.fkey1, ref.fkey2, ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11, ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15, ref.fkey16)
AND c_obj.id = ref.constid
AND t_obj.name = @TableName
AND col.name = a.name
) [CheckTable],
-- Get Indexes Field
(SELECT 1 FROM sysobjects c_obj, syscolumns col, sysindexkeys i
WHERE c_obj.uid = user_id()
AND c_obj.id = col.id
AND col.id = i.id
AND c_obj.xtype = 'U'
AND col.colid = i.colid
AND c_obj.name = @TableName
AND col.name = a.name) [IsIdx]
--
FROM syscolumns a join systypes b on a.xusertype=b.xusertype
WHERE id=object_id(@tableName) ORDER BY a.colorder
END
ELSE
PRINT 'ERROR: No table named '+@tableName + ' found in ' + db_name() + ' database.'
GOCan you help me to modify it? ... thanks a lot
-
Change to this in the Get Indexes Field section
-- Get Indexes Field
(SELECT 1 FROM sysobjects c_obj, syscolumns col, sysindexes i
WHERE c_obj.uid = user_id()
AND c_obj.id = col.id
AND col.id = i.id
AND c_obj.xtype = 'U'
AND col.colid = i.indid
AND c_obj.name = @TableName
AND col.name = a.name AND
indid > 0 and indid < 255 and
(i.status & 64)=0) [IsIdx]
--
Cam about this by looking at the contents of sp_helpindex using sp_helptext in QA.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
thanks a lot ... but i need modified a little bit ...
-----
(SELECT 1 FROM sysobjects c_obj, syscolumns col, sysindexes i, sysindexkeys k
WHERE c_obj.uid = user_id()
AND c_obj.id = col.id
AND col.id = i.id
AND col.id = k.id
AND c_obj.xtype = 'U'
AND col.colid = k.colid
AND i.indid = k.indid
AND c_obj.name = @TableName
AND col.name = a.name
AND i.indid > 0
AND i.indid < 255
AND (i.status & 64)=0) [IsIdx],-----
now can work properly ... thanks for your help ... anyway ... may i know again from you how to get the default value ?
in my code is :
---
(SELECT comm.text FROM sysobjects c_obj, sysobjects t_obj, syscolumns col, syscomments comm
WHERE c_obj.uid = user_id()
AND c_obj.xtype = 'D'
AND t_obj.id = c_obj.parent_obj
AND t_obj.id = col.id
AND col.colid = comm.id
AND c_obj.id = comm.id
AND t_obj.name = @TableName
AND col.name = a.name) [Default Val]---
thanks for your help ...
-
for the default value, I have found the solution.
(SELECT comm.text FROM sysobjects c_obj, syscolumns col, syscomments comm
WHERE c_obj.uid = user_id()
AND c_obj.xtype = 'U'
AND c_obj.id = col.id
AND col.cdefault = comm.id
AND c_obj.name = @TableName
AND col.name = a.name) [Default Val]btw, thanks for your help ...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply