ALTER  procedure dev_get_table_primary_key (
	@table_name varchar(50)
) as
	select o.name as [tablename], c.*
	from 
		sysindexkeys k
		inner join sysobjects o on o.id = k.id
		inner join syscolumns c on c.id = o.id and c.colid = k.colid
	where
		o.xtype = 'U'
		and o.name = RTRIM(LTRIM(@table_name))


GO

	select 
		t_obj.name as [TableName], 
		col.name as [ColumnName], 
		typ.name as [Type],
		col.length as [Length],
		col.isnullable as [Nullable],
		col.colid as [OrdinalPosition],
		if col.name in ( 
			select 
				pkc.name
			from 
				sysindexkeys k, syscolumns pkc
			where 
				pkc.id = k.id 
				and pkc.colid = k.colid 
				and k.id = t_obj.id
		)
			begin
			select [IsPK] = 1
			end
		else 
			begin
			select [IsPK] = 0
			end
	from 
		sysobjects t_obj
		inner join syscolumns col on col.id = t_obj.id
		inner join systypes typ on typ.xtype = col.xtype
	where 
		t_obj.xtype = 'U'
	order by 
		[TableName], [OrdinalPosition]

quote:

---

Originally posted by chacha

I am writing a code generator for work. Nothing fancy, but it needs to convert sqlserver tables to c# classes which will play nice with our proprietary framework.

---

select 
	t_obj.name as [TableName], 
	col.name as [ColumnName], 
	typ.name as [Type],
	col.length as [Length],
	col.isnullable as [Nullable],
	col.colid as [OrdinalPosition],
	CASE
		WHEN col.name IN(
			select pkc.name
			from sysindexkeys k, syscolumns pkc
			where pkc.id = k.id 
			and pkc.colid = k.colid 
			and k.id = t_obj.id
			) THEN 1
		ELSE 0
	END as [IsPK]
from    
	sysobjects t_obj
	inner join syscolumns col on col.id = t_obj.id
	inner join systypes typ on typ.xtype = col.xtype
	--left outer join sysindexkeys k on k.id = t_obj.id	-- any keys	
	--left outer join syscolumns pkc on (			-- the column info for the key
	--	pkc.id = k.id 
	--	and pkc.colid = k.colid
	--)
where 
	t_obj.xtype = 'U'
	/*and t_obj.name LIKE 'bk_ac_%'*/
order by 
	[TableName], [OrdinalPosition]

quote:

---

Originally posted by nr

Did you read my post about that not giving PK cols? Don't think it does anyway.

---

USE Northwind
GO

-- SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name

DECLARE	@strTable	varchar(256)
SELECT	@strTable = 'Order Details'

SELECT	C.COLUMN_NAME,
	C.ORDINAL_POSITION,
	C.DATA_TYPE,
	C.CHARACTER_MAXIMUM_LENGTH,
	C.NUMERIC_PRECISION,
	C.NUMERIC_SCALE,
	[PKID] = KCU.ordinal_position,
	[IsIdentity] = 0,
	[IsCalculated] = 0,
	[IsNullable] = 0,
	[DefaultValue] = CONVERT(varchar(7000), NULL)
INTO	#TEMP_TABLE_02
FROM	INFORMATION_SCHEMA.TABLES T
	JOIN INFORMATION_SCHEMA.COLUMNS C
		 ON C.TABLE_CATALOG = T.TABLE_CATALOG 
		AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
		AND C.TABLE_NAME = T.TABLE_NAME
	LEFT OUTER JOIN INFORMATION_SCHEMA.table_constraints TC 
		 ON TC.TABLE_CATALOG = T.TABLE_CATALOG
		AND TC.TABLE_SCHEMA = T.TABLE_SCHEMA
		AND TC.TABLE_NAME = T.TABLE_NAME
		AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
	LEFT OUTER JOIN INFORMATION_SCHEMA.key_column_usage KCU 
		 ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
		AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
		AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
		AND KCU.COLUMN_NAME = C.COLUMN_NAME
WHERE	T.TABLE_NAME = @strTable
ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION

UPDATE	T1
SET	[IsIdentity] = CASE WHEN C.colstat = 1 THEN 1 ELSE 0 END,
	[IsCalculated] = CASE WHEN C.colstat = 4 THEN 1 ELSE 0 END,
	[IsNullable] = C.isnullable,
	[DefaultValue] = CONVERT(varchar(7000), COM.[text])
FROM	#TEMP_TABLE_02 T1
	JOIN SYSOBJECTS O 
		 ON O.name = @strTable
		AND O.Type = 'U'
	JOIN dbo.syscolumns C
		 ON O.id = C.ID
		AND C.Name = COLUMN_NAME
	LEFT OUTER JOIN syscomments COM
		 ON COM.ID = C.cdefault

SELECT * FROM #TEMP_TABLE_02 ORDER BY ORDINAL_POSITION

GO
DROP TABLE #TEMP_TABLE_02
GO

select 
	t_obj.name as [TableName], 
	col.name as [ColumnName],
	CASE
		WHEN col.name IN(
			select pkc.name
			from sysindexkeys k, syscolumns pkc
			where pkc.id = k.id 
			and pkc.colid = k.colid 
			and k.id = t_obj.id
			) THEN 1
		ELSE null
	END as [IsPK],
	CASE col.colstat
		WHEN 1 then 1
		ELSE 0
	END as [IsIdentity], 
	typ.name as [Type],
	col.length as [Length],
	col.scale as [Scale],
	col.prec as [Precision],
	col.isnullable as [Nullable],
	col.colid as [OrdinalPosition],
	col.iscomputed as [IsComputed],
	com.ctext as [SqlRule]
from    
	sysobjects t_obj
	inner join syscolumns col on col.id = t_obj.id
	inner join systypes typ on typ.xtype = col.xtype
	left outer join syscomments com on (com.id = col.id and com.number = col.colid)
where 
	t_obj.xtype = 'U'
	and t_obj.name LIKE 'bk_%'
order by 
	[TableName], [OrdinalPosition]

quote:

---

Originally posted by chacha

I eventually needed the other three key pieces of info you supplied - to know whether the pk columns were identity or not, what the default values for the columns are, and whether the column is calculated or not

---

quote:

---

Originally posted by chacha

quote:

---

Originally posted by nr

Did you read my post about that not giving PK cols? Don't think it does anyway.

---

Currently only primary keys are being returned and the results on a complex schema of ~100 tables is perfect. But I think you are right, we just haven't indexed tables yet, b/c there is not data in the tables at this time. But if I really want to make sure that only pk's are returned into the set I could just join sysindexes and sysobjects to sysindexkeys, restricting the join to sysobjects where xtype = 'PK', right?

---