Description Metadata

When creating columns in MS-SQL there is a description field. How can I quer
y
this description field? I know how to return the Table Name, Column Name,
Data Type, Is_Nullable, etc. but I don’t know how to return the Descriptio
n.> When creating columns in MS-SQL there is a description field.
This is only ifyou create the columns in Enterprise Manager *and* choose to
use that property. Personally, I don't think you should do either, mostly
because your CREATE TABLE statements should be something that you can
script, modify, store in source control, etc.
In any case:
http://www.aspfaq.com/2244|||Aaron, good point! So where would you store the description of individual
columns? Would you create separate tables to store the descriptions of the
tables and the columns?|||> Aaron, good point! So where would you store the description of individual
> columns? Would you create separate tables to store the descriptions of the
> tables and the columns?
Why do you feel the description of your database schema needs to be stored
in the database?
We create separate human readable documentation. This allows us much more
freedom in how we are describing the schema, e.g. definitions > 64
characters, diagrams, tables, etc.
A|||I was thinking of creating a web app so that the descriptions, rules, etc.
can be documented, searched, and studied by several disparate groups. As of
now the database is completely undocumented. Then to complicate matters even
more, the knowledge of this database is scattered amongst many different
groups and individuals who won’t always work together. I keep running into
columns named TX5_RRT_SDescr and no one knows what the hell that column is
used for. The DBA is too busy to answer most questions and his answers are
too flipped to be of much use.
What form in your documentation in?|||Most of ours are in Word format. But you can make your job easier using an
extraction tool, for example Enterprise Architect.
Hey, if the DBA is too busy to answer questions that need to be answered,
why do you think he'd be willing to populate some table with the answers?
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won’t always work together. I keep running
> into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Lol, he won’t! The rest of us peons will have to collaboratively piece thi
ngs
together. There are about 5 of us who have volunteered for this project and
I
just need to figure out how to best do it. I have a zero budget to work with
.
Ok then your suggestion is not to store the description in database
properties. Thanks.
The answer to my original post is:
Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
SELECT name, CONVERT(varchar(2000), [value]) AS Description
FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
'admin_Users', 'Column', 'User_ID')|||> The answer to my original post is:
> Where Table name is ‘Admin_Users’ and the column name is ‘User_ID’
> SELECT name, CONVERT(varchar(2000), [value]) AS Description
> FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',
> 'admin_Users', 'Column', 'User_ID')
Yep, I posted a link to code samples earlier:
http://www.aspfaq.com/2244|||You could store your meta data in SQL Server tables and then develop your
own custom GUI and reports around it, but there are many 3rd party
applications would do a better job.
Tools like ERWin, Viseo (or even Enterprise Managers Diagramer) can be used
to create annotated diagrams of the database model
http://www.databaseanswers.com/modelling_tools.htm
Reporting Services 2005 has a Model Designer that provides end users with a
high level view of the database model for use with Report Builder.
http://www.devx.com/dbzone/Article/28047/1954?pf=true.
"Tome73" <Tome73@.discussions.microsoft.com> wrote in message
news:200AB1D7-AD01-426A-B580-7CE42716BBDE@.microsoft.com...
>I was thinking of creating a web app so that the descriptions, rules, etc.
> can be documented, searched, and studied by several disparate groups. As
> of
> now the database is completely undocumented. Then to complicate matters
> even
> more, the knowledge of this database is scattered amongst many different
> groups and individuals who won't always work together. I keep running into
> columns named TX5_RRT_SDescr and no one knows what the hell that column is
> used for. The DBA is too busy to answer most questions and his answers are
> too flipped to be of much use.
> What form in your documentation in?|||Aaron, yes you did and I missed it at first. I found
http://developer.com/db/article.php/3361751 which is basically the same.
Hey thanks Aaron for all your help and insight.sql

describe table

Oracle has describe table, which will return the SQL Definition of that tabl
e...what is the equivalent in MSSQL? I am aware of sp_help table, but that d
oesn't cut it.
I want to create a table using one of the various GUI tools that are "out th
ere" but I then want to save the resulting SQL DDL, so that I can script fut
ure database creates/drops/etc.
Any help appreciated.
BOBBoth Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
David Portas
SQL Server MVP
--|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually,
I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:

> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
>
>|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for
an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to
start at:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am
using MSDE, so EM is not
an option...)[vbcol=seagreen]
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
>|||One of the ten commandments at our shop is to SCRIPT EVERYTHING.
That means we make a TEXT file with an extension of .SQL and put our TABLE c
reate right in there. We building indexes and constraints right in that scr
ipt. We have control over naming our indexes.
We script STORED PROCEDURE creation. We script the GRANTing of rights to SP
ROCS in the same script.
Changes made in the GUI can get lost and never make it to production servers
.
Having EM or QA create a script from the database is flawed - it doesn't hav
e everything associated with that table or sproc...
At least that's the way we do it - and it's been very successful in a rapid
development/get the software to the customer scenario over the past 3 years.
"David Portas" wrote:

> Query Analyzer will script any table individually. Just right-click on the
> table in the Object Browser and select Script to New Window As > Create.
> Enterprise Manager will allow you to script multiple objects to a single
> file or script an entire database if you wish.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

describe table

Oracle has describe table, which will return the SQL Definition of that table...what is the equivalent in MSSQL? I am aware of sp_help table, but that doesn't cut it.
I want to create a table using one of the various GUI tools that are "out there" but I then want to save the resulting SQL DDL, so that I can script future database creates/drops/etc.
Any help appreciated.
BOB
Both Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB
|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB
|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
David Portas
SQL Server MVP
|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:

> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
>
>
|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to start at:
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not
an option...)[vbcol=seagreen]
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
|||One of the ten commandments at our shop is to SCRIPT EVERYTHING.
That means we make a TEXT file with an extension of .SQL and put our TABLE create right in there. We building indexes and constraints right in that script. We have control over naming our indexes.
We script STORED PROCEDURE creation. We script the GRANTing of rights to SPROCS in the same script.
Changes made in the GUI can get lost and never make it to production servers.
Having EM or QA create a script from the database is flawed - it doesn't have everything associated with that table or sproc...
At least that's the way we do it - and it's been very successful in a rapid development/get the software to the customer scenario over the past 3 years.
"David Portas" wrote:

> Query Analyzer will script any table individually. Just right-click on the
> table in the Object Browser and select Script to New Window As > Create.
> Enterprise Manager will allow you to script multiple objects to a single
> file or script an entire database if you wish.
> --
> David Portas
> SQL Server MVP
> --
>
>

describe table

Oracle has describe table, which will return the SQL Definition of that table...what is the equivalent in MSSQL? I am aware of sp_help table, but that doesn't cut it.
I want to create a table using one of the various GUI tools that are "out there" but I then want to save the resulting SQL DDL, so that I can script future database creates/drops/etc.
Any help appreciated.
BOBBoth Query Analyzer and Enterprise Manager can script DDL from an existing
objects. EM also allows you to save DDL scripts when creating or modifying
objects via the 'save change script' button.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||If you use Enterprise Manager (one of those various GUI tools ;-)) you can
generate a create table script with the third button from the left.
--
Jacco Schalkwijk
SQL Server MVP
"bob" <bob@.discussions.microsoft.com> wrote in message
news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> Oracle has describe table, which will return the SQL Definition of that
table...what is the equivalent in MSSQL? I am aware of sp_help table, but
that doesn't cut it.
> I want to create a table using one of the various GUI tools that are "out
there" but I then want to save the resulting SQL DDL, so that I can script
future database creates/drops/etc.
> Any help appreciated.
> BOB|||Query Analyzer will script any table individually. Just right-click on the
table in the Object Browser and select Script to New Window As > Create.
Enterprise Manager will allow you to script multiple objects to a single
file or script an entire database if you wish.
--
David Portas
SQL Server MVP
--|||Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not an option...)
Cheers,
BOB
"Jacco Schalkwijk" wrote:
> If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> generate a create table script with the third button from the left.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "bob" <bob@.discussions.microsoft.com> wrote in message
> news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> > Oracle has describe table, which will return the SQL Definition of that
> table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> that doesn't cut it.
> >
> > I want to create a table using one of the various GUI tools that are "out
> there" but I then want to save the resulting SQL DDL, so that I can script
> future database creates/drops/etc.
> >
> > Any help appreciated.
> >
> > BOB
>
>|||Bob,
There's no TSQL out-of-the-box option to get the CREATE TABLE statement for an existing table. There are bunch
of options, both GUI, client code and TSQL code, however. You might want to start at:
http://www.karaszi.com/sqlserver/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bob" <bob@.discussions.microsoft.com> wrote in message
news:A51ADE49-037D-4CD6-85AB-E169CB524DAF@.microsoft.com...
> Thanks...I was hoping for a non EM solution...a simple bit of SQL (actually, I am using MSDE, so EM is not
an option...)
> Cheers,
> BOB
> "Jacco Schalkwijk" wrote:
> > If you use Enterprise Manager (one of those various GUI tools ;-)) you can
> > generate a create table script with the third button from the left.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "bob" <bob@.discussions.microsoft.com> wrote in message
> > news:BA0FCA03-FD78-4845-9655-00CE08A5B290@.microsoft.com...
> > > Oracle has describe table, which will return the SQL Definition of that
> > table...what is the equivalent in MSSQL? I am aware of sp_help table, but
> > that doesn't cut it.
> > >
> > > I want to create a table using one of the various GUI tools that are "out
> > there" but I then want to save the resulting SQL DDL, so that I can script
> > future database creates/drops/etc.
> > >
> > > Any help appreciated.
> > >
> > > BOB
> >
> >
> >|||if you want full database change management for SQL
Server check out www.dbghost.com. They also give away a
free scripting utility which scripts out data and schema.
>--Original Message--
>Oracle has describe table, which will return the SQL
Definition of that table...what is the equivalent in
MSSQL? I am aware of sp_help table, but that doesn't cut
it.
>I want to create a table using one of the various GUI
tools that are "out there" but I then want to save the
resulting SQL DDL, so that I can script future database
creates/drops/etc.
>Any help appreciated.
>BOB
>.
>

Desapering rows

Hi.

I have an mdx query which return some rows and for some values there are null values for 1 column (it's ok). But when the report is rendered have disapeared all these rows. I need to show these rows because they have value for other columns.

Thanks in advance.I refresh the datasource by editing the query and refresh and that's all.

I don't know what happend.

Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part

SELECT DISTINCT c.Company
FROM Customers as c
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'X') as temp1
 ON c.CustomerID = temp1.CustomerID
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'Y') as temp2
 ON c.CustomerID = temp2.CustomerID

Derived Column return WRONG datatype.

Hi, friend,
l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

This should not be a problem - you can map an integer in the data flow to a float at the destination without any issues.

Donald Farmer

|||Fact is, l get a Integer at the end :(, even the data and meta data is float........
|||

Try casting A and B as floats before using them in your calculation.

-Jamie