Search DB Schema

Share your SQL code snippets for SQL Prompt!

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson, Paul Stephenson

Search DB Schema

Postby atomicbomb » Thu Feb 14, 2008 5:17 am

Description: Generates sql code to search the schema of a database, useful for big db's where tables have lots of columns, and you can't remember which table it's in :)

Shortcut: sis (search in schema)

Code:
-- Set the database
Use [put your db here]

-- Exclude views and variable names
Select S.Name, T.Name, O.Name As [Object Name]
From SysColumns S
Inner Join SysObjects O on S.ID = O.ID
Inner Join SysTypes T On T.XType = S.XType
Where {(}S.Name Like '{%}name{%}'{)}
And {(}O.Name Not Like 'vw{%}'{)}
And {(}S.Name Not Like '@{%}'{)}
atomicbomb
 
Posts: 2
Joined: Thu Feb 14, 2008 5:14 am

Return to SQL Prompt code snippets

Who is online

Users browsing this forum: No registered users and 1 guest