Sql Server : Finding tables that use small int

N_Sri
N_Sri used Ask the Experts™
on
Hi ,
 Please help to write a query  to find all the tables in my Database , which has  small int has datatype and there column names
once again Find all tables  which have column  datatype as smallint and the column names.
Quick response is highly appreciable.



Thankyou
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
try this...

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
            AND systypes.name LIKE '%smallint%'
ORDER BY sysobjects.name,syscolumns.colid
Commented:
hi,
 Please try this.
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='SMALLINT'

Open in new window

Author

Commented:
thankyou

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial