Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: sql server & col data types

    I am trying to write a generic stored procedure to return the data type of a given col in a given table in any database.

    I would like to place this sp in master.

    input parms are @TableName sysname and @ColName sysname
    ouput parm is @data_type varchar(128)

    set @data_type=''
    select @data_type = data_type from information_schema.columns where table_name = @TableName and column_name = @ColName

    when debugging this I always get 0 rows (@data_type remains '')

    I have a thought that I can add an input parm for the database, say @DbName sysname
    But I am having trouble updating the select stmt to be something like
    ...from @DbName +'.' + information_schema.columns.

    Any Ideas?
    Thanks for your help.

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    You shold do something like this:

    declare @mystmt varchar(255)

    Set @mystm = ' your sql string is prepared here'

    execute sp_executesql @mystmt

    See also the manual for sp_executesql

    Marp

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE PROC usp_WhatType 
    	  @TABLE_CATALOG	sysname
    	, @TABLE_SCHEMA		sysname
    	, @TABLE_NAME		sysname 
    	, @COLUMN_NAME		sysname
    	, @DATA_TYPE		varchar(20)	OUTPUT
    AS
    BEGIN
    	SET NOCOUNT ON
    	DECLARE @SQL varchar(8000)
    	CREATE TABLE #dt(Col1 varchar(20))
    	SET @SQL = 'INSERT INTO #dt(Col1) SELECT DATA_TYPE FROM ' + '['
    		+   @TABLE_CATALOG + '].INFORMATION_SCHEMA.COLUMNS'
    		+ ' WHERE TABLE_SCHEMA = '	+ '''' + @TABLE_SCHEMA + ''''
    		+ ' AND TABLE_NAME = ' 	+ '''' + @TABLE_NAME + ''''
    		+ ' AND COLUMN_NAME = ' 	+ '''' + @COLUMN_NAME + ''''
    	EXEC(@SQL)
    	SELECT @DATA_TYPE = Col1 FROM #dt
    	DROP TABLE #dt
    	SET NOCOUNT OFF
    END
    GO
    
    DECLARE   @TABLE_CATALOG	sysname
    	, @TABLE_SCHEMA		sysname
    	, @TABLE_NAME		sysname 
    	, @COLUMN_NAME		sysname
    	, @DATA_TYPE		varchar(20)
    
    SELECT 	  @TABLE_CATALOG 	= 'Northwind'
    	, @TABLE_SCHEMA		= 'dbo'
    	, @TABLE_NAME		= 'Order Details'
    	, @COLUMN_NAME		= 'OrderId'
    
    EXEC usp_WhatType 
    	  @TABLE_CATALOG
    	, @TABLE_SCHEMA
    	, @TABLE_NAME
    	, @COLUMN_NAME
    	, @DATA_TYPE OUTPUT
    
    SELECT @DATA_TYPE
    GO
    
    DROP PROC usp_WhatType
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    When you say - data type of a given col in a given table in any database - are you looking to test this col/table for all databases or are you just looking at 1 database ? In your example, it seems that the information_schema.columns view would be sufficient - why do you want to create a stored procedure ?

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    rnealejr,
    I want to use the functionality from various places so my thinking is that I want to call my own routine (the view may change someday - I will only have to update my sp - I won't have to hunt down all the selects from information_schema).

    The select as given did work fine UNTIL I moved the sp it was in from my db to master - then suddenly it returned 0 rows.

    I am guessing that when I debug my sp from query analyzer it seems to use master and then execute - could that be true.

    Also I named my sp sp_blah (note sp_ prefix) so it should execute in the context of the current db (that was my thinking anyway).

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    If the view may change someday, then create your own information_schema view - so you can have the abstraction you desire.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The view is specific to the database...did you try the code I posted?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Posts
    7
    Originally posted by Brett Kaiser
    The view is specific to the database...did you try the code I posted?
    Yes, works like a charm of course. I was just hoping to avoid temp table.
    Thanks!

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    FYI - You can use sp_Msforeachdb to loop through all databases.
    Also, I would recommend creating your own information_schema view based on your requirements rather than a stored procedure.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rnealejr
    FYI - You can use sp_Msforeachdb to loop through all databases.
    Also, I would recommend creating your own information_schema view based on your requirements rather than a stored procedure.
    How would you do that when you would have to cross catalogs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •