include schema in the follow query

N_Sri
N_Sri used Ask the Experts™
on
I want include schema field in the below query.

like my tables are in  Abc schema, and bbc schema

i.e Table ,Schema name .....

SELECT  o.name AS TableName,c.name AS Columnname, c.xtype, T.name,o.type
 FROM syscolumns AS c INNER JOIN
 sysobjects AS o ON c.id = o.id INNER JOIN
 systypes AS T ON c.x[code]type = T.xtype
 WHERE T.name ='smallint' and o.type='U'  
 ORDER BY o.name 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Join to systable and syschema table to to get schema name for the table you are trying to query.

Author

Commented:
Can you please modify the query and let me know
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
Select * from INFORMATION_SCHEMA.TABLES
Select * from INFORMATION_SCHEMA.COLUMNS
Commented:
Hi,
 Please try this.
SELECT  TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

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