Benötige Hilfe bei einer SP

[TheJoe]

Ich stehe vor dem Problem, zu einer SP die die Felder Namen, Type und Primary Key so erweitert, dass sie auch noch die Beschreibung ausliest.

--***********************************************************************
--                              GET_FIELDS
--***********************************************************************
CREATE PROCEDURE [dbo].[get_fields] 
	@database_name varchar(100), 
	@table_name varchar(100)
AS
BEGIN

  DECLARE @sqlstatement NVARCHAR(700)
  SET @sqlstatement = 
	'select columns.COLUMN_NAME, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.NUMERIC_PRECISION, 
	(select table_constraints.CONSTRAINT_TYPE from (' + @database_name + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS table_constraints 
	inner join ' + @database_name + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE key_column_usage on table_constraints.CONSTRAINT_NAME = key_column_usage.CONSTRAINT_NAME)
	where table_constraints.TABLE_NAME = '''+ @table_name + ''' and key_column_usage.COLUMN_NAME = columns.COLUMN_NAME and table_constraints.CONSTRAINT_TYPE = ''PRIMARY KEY'') as ''Primary Key''
	FROM ' + @database_name + '.INFORMATION_SCHEMA.COLUMNS columns
	where columns.TABLE_NAME = ''' + @table_name + '''
	order by columns.COLUMN_NAME'

  EXEC sp_executesql @sqlstatement
END
GO

So sah sie vorher aus.

Ich habe jetzt einfach das Ergebniss von dazugejoined:

--***********************************************************************
--                              GET_FIELDS
--***********************************************************************
CREATE PROCEDURE [dbo].[get_fields] 
	@database_name varchar(100), 
	@table_name varchar(100)
AS
BEGIN

  DECLARE @sqlstatement NVARCHAR(700)
  SET @sqlstatement = 
	'select columns.COLUMN_NAME, columns.DATA_TYPE, columns.CHARACTER_MAXIMUM_LENGTH, columns.NUMERIC_PRECISION, 
	(select table_constraints.CONSTRAINT_TYPE from (' + @database_name + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS table_constraints 
	inner join ' + @database_name + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE key_column_usage on table_constraints.CONSTRAINT_NAME = key_column_usage.CONSTRAINT_NAME)
	where table_constraints.TABLE_NAME = '''+ @table_name + ''' and key_column_usage.COLUMN_NAME = columns.COLUMN_NAME and table_constraints.CONSTRAINT_TYPE = ''PRIMARY KEY'') as ''Primary Key'',value

	FROM  ('+ @database_name + '.INFORMATION_SCHEMA.COLUMNS columns) LEFT JOIN (::fn_listextendedproperty(NULL, "´schema", "dbo", "table", ' + @table_name + ', "column", default)) ON (columns.COLUMN_NAME = ::fn_listextendedproperty(NULL, "´schema", "dbo", "table", ' + @table_name + ', "column", default).objname )
	where columns.TABLE_NAME = ''' + @table_name + '''
	order by columns.ORDINAL_POSITION'

  EXEC sp_executesql @sqlstatement
END
GO

Jetzt steh ich vor der Situation, dass ich den ganzen Nachmittag herumprobiert habe aber absolut nichts funktioniert...

Vielleicht könnte sich irgendwer den Code anschauen, es wäre mir wirklich eine große Hilfe.

mfg

[ebis]

so kommt wenigstens was raus, wenn man denn extended-Properties hat...

[HIGHLIGHT="sql"]--************************************************** *********************
-- GET_FIELDS
--************************************************** *********************
CREATE PROCEDURE dbo.get_fields
( @database_name SYSNAME
. @table_name SYSNAME
, @SchemaName SYSNAME = N'dbo'

AS
BEGIN

DECLARE @sqlstatement NVARCHAR(MAX)
SET @sqlstatement = N'select c.COLUMN_NAME
, c.DATA_TYPE
, c.CHARACTER_MAXIMUM_LENGTH
, c.NUMERIC_PRECISION
, (select tc.CONSTRAINT_TYPE
from ' + @database_name + N'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
join ' + @database_name +N'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu on Tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where tc.TABLE_NAME = ''' + @table_name +N'''
and kcu.COLUMN_NAME = c.COLUMN_NAME
and tc.CONSTRAINT_TYPE = ''PRIMARY KEY'') as ''Primary Key''
---- , E1.value
FROM ' + @database_name + N'.INFORMATION_SCHEMA.COLUMNS c
CROSS APPLY fn_listextendedproperty(NULL, ''schema'', ''' + @SchemaName +N''', ''table'', ''' + @table_name + N''', ''column'', default) AS E1
CROSS APPLY fn_listextendedproperty(NULL, ''schema'', ''' + @SchemaName +N''', ''table'', ''' + @table_name +N''', ''column'', default) AS E2
where c.TABLE_NAME = ''' + @table_name +N'''
and C.COLUMN_NAME COLLATE Latin1_General_CI_AI = E2.objname COLLATE Latin1_General_CI_AI
order by c.ORDINAL_POSITION;'
EXEC sp_executesql @sqlstatement
RETURN
END
[/HIGHLIGHT]