Solved

SQL Select Databases information

Posted on 2012-04-12
3
178 Views
Last Modified: 2012-06-27
Is there a select or view that I could get that gets me the following for all NON system databases?

Such as if I had 2 catalogs....busSys and busUsers, I would get JUST the following for those two databases?

databasename     tablename     fieldname     fieldtype     isnull
0
Comment
Question by:lrbrister
  • 2
3 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37839993
In SQL you can use

select * from sys.tables
select * from sys.columns

to get info about tables/columns in each user database
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37840018
For instance code below will list all tables,columns, data type and precision/scale from current db where is executed :

select distinct
      c.id,
      o.name as 'Tname',
      c.name as 'Cname',
      t.name as DataType, c.length, c.xprec, c.xscale, c.isnullable--into #cp_tabless
from sysobjects o, syscolumns c, systypes t
where o.id = c.id and
          c.xtype = t.xtype and
            t.name <> 'sysname' and o.name not like 'sys%' and
        o.type = 'U'  
order by 'cname','tname'
0
 

Author Closing Comment

by:lrbrister
ID: 37857109
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now