Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hdv212
Posting Yak Master
140 Posts |
 Posted - 2008-04-21 : 16:57:09
|
hii want to get all columns of database, i use this code to do this :select * from information_schema.columns this script works good,but return columns for all tables and views and i don't views, i want only columns for user tables, also i don't know how to filter this result to remove views, can anybody help me ?thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-21 : 17:28:44
|
| where table_name in (select table_name from information_schema.tables where table_type = 'base_table')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-21 : 17:29:37
|
| select c.* from information_schema.columns cinner join information_schema.tables ton c.table_name = t.table_namewhere t.table_type = 'base table'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-04-21 : 17:53:48
|
The two solutions posted above should also include a join on column TABLE_SCHEMA, in case there are tables not owned by DBO in the database.Here is another solution without a join. This must be run in the database you are interested in.Use MyDBselect *from information_schema.columns awhere object_id(quotename(a.TABLE_SCHEMA)+'.'+quotename(a.TABLE_NAME),'U') is not nullorder by a.TABLE_SCHEMA, a.TABLE_NAME, a.ORDINAL_POSITION CODO ERGO SUM |
|
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2008-04-22 : 02:54:00
|
| very very thanks. |
|
|
|
|
|
|
how to get all columns of database ?