Post reply

Datatype stored

  • June 7, 2012 at 10:17 am

    #275219

    Can anybody let me know if is there any way to pull which table has got particualr datatype in a database?

    For example,

    We have a database 'advance', this database has got 40 tables. The question is out of 40 tables which tables has got 'image' as datatype? with lesser effort?

    Please advise

    Thanks.

  • June 7, 2012 at 10:24 am

    #1498294

    Sure

    DECLARE @DataType SYSNAME;

    SET @DataType = 'image';

    SELECT OBJECT_NAME(object_id) AS TableName ,

    c.name AS ColumnName ,

    t.name AS Type

    FROM sys.columns AS c

    INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

    WHERE t.name = @DataType;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 7, 2012 at 10:54 am

    #1498314

    Thanks A lot.

  • June 7, 2012 at 12:15 pm

    #1498347

    You can also take a look at the free Red Gate utility SQL Search[/url]. It'll hit all the databases at the same time.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply