Hope this helps,
Vaughn|||Thanks Vaughn. But when I query this view, it also returns some tables those are not created by myself such as dtproperties table and sysdiagrams table. How can I filter them out?|||
Depending on what exactly you're trying to accomplish you may want to filter out tables for which the "is_ms_shipped" column is "1" or you may want to join against the "sys.schemas" table and filter the results to certain schemas (e.g. owners).
Hope this helps,
Vaughn
Hope this helps,
Vaughn|||Thanks Vaughn. But when I query this view, it also returns some tables those are not created by myself such as dtproperties table and sysdiagrams table. How can I filter them out?|||
Depending on what exactly you're trying to accomplish you may want to filter out tables for which the "is_ms_shipped" column is "1" or you may want to join against the "sys.schemas" table and filter the results to certain schemas (e.g. owners).
Hope this helps,
Vaughn
Given a database table name, how do I get all the column names of the table? Actually my task is more than that, what I want is the percentage of null values for each column. But there too many columns in the table and I don't want to type the column name one by one. How to achieve it?
For example, suppose I have a table TB which contains 50 columns C1 to C50. What I want to produce a stored-procedure that table the table name as input parameter and output the percentage of null values for each column C1 to C50, something like:
ColumnName NullPercentage
C1 14.28%
....
C50 6.89%
Thank you!
You can get the column names from system table SYSCOLUMNS.
You can use them to construct a dynamic SQL statement, and then use EXEC(@.string_variable) to run the query.
If you need further assistance, I can try to hunt up some code where I did this sort of thing to estimate the space consumed by a table. (Now with SQL Server 2005 you can find such space information, both for the table and its indexes, with "Properties" after right-clicking on the table name in SSMS.)
HTH.
Dan
|||I am fairy new to sql. If you could have some sample script that would be great! Thanks.I'll see if I can post the code tomorrow, Friday. Perhaps others here may beat me to it.
Dan
|||Yes.. Here you go...
Hey Dan I never thought to beat you Buddy .. 
Code Snippet
Declare @.Cols as Varchar(8000);
Declare @.PreparedCols as Varchar(8000);
Declare @.TableName as NVarchar(1000);
Select @.TableName = 'Orders'
Select
@.PreparedCols = ',Cast(Sum(Case When ? is null Then 1 Else 0 End)/Sum(1.0) * 100 as Numeric(5,2)) as [?]',
@.Cols = ''
Select
@.Cols = @.Cols + replace(@.PreparedCols, '?', name)
From
syscolumns
Where
id=Object_Id(@.TableName)
Exec ('Select ''NULL Value %'' as Comment' + @.Cols + ' From ' + @.TableName)
|||Manivannen,
Not only did you beat me, but you provided a much-more compact solution!
I have never before built a dynamic SQL statement using a SELECT to append to an ever-growing SQL statement.
I like it!
Thanks for the education!
Where I tried it, though, I would like to note that I had to change @.COL to VARCHAR(MAX); it must have changed @.COL to NVARCHAR, since I could only print 4000 characters of it. It worked fine when I changed to VARCHAR(MAX). (Without the change the limit is around 40 columns, depending on the lengths of the column names.)
Dan
P.S. Is that enough exclamation points?
I had to change to NVarchar(MAX) too, otherwise some of the tables will not work.
I marked both posts as answer.
Thanks!
|||
Thanks for your question, too!
I learn a lot by reading answers in this Forum.
Dan
I would like to get a list of all the table names from a database, can
anybody please tell me how to do that? Many thanks!See sp_tables and sysobjects in Books Online.
Simon|||Select table_name from information_schema.tables
where table_type='Base table' order by table_name
Madhivanan|||Hi, you can write it as:
SELECT name [Table Name]
FROM sysobjects
WHERE xtype = 'U'
Please let me know if this helped
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------
*** Sent via Developersdex http://www.developersdex.com ***|||Chandra, It is better to use views and avoid using system tables
Madhivanan
--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal
It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION
If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.
Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
Hello,
To get a list of user tables use this query:
select name from ssysobjects where type = 'u'
And for a list of columns related for all objects (tables,views,sps)
select name, object_name(id) from sysindexes
The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.
Regards,
-Manoj
