Showing posts with label northwind. Show all posts
Showing posts with label northwind. Show all posts

Monday, March 26, 2012

Gather Meta Data

Hi,

I would like to prepare a data dictionary for my database (northwind).

I have framed the below SQL

SELECT
'NAME ' = a.name,
'DESCRIPTION' = b.value,
'Type ' = type_name(a.xusertype),
' ' AS 'Values',
'NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,
' ' AS 'PK',
' ' AS 'FK'
FROM
syscolumns a,
sysproperties b

WHERE
a.id = 2073058421 AND -- Customers Table
a.number = 0 AND
b.id = a.id AND
b.smallid = a.colid

ORDER BY a.colid

and the output would be:

NAME DESCRIPTION Type Values NULL PK FK
----- ----- ---- -- -- -- --
CustomerID Customer ID nchar
CompanyName Company Name nvarchar
ContactName Contact Name nvarchar X
ContactTitle Contact Title nvarchar X
Address Address nvarchar X
City City nvarchar X
Region Region nvarchar X
PostalCode Postal Code nvarchar X
Country Country nvarchar X
Phone Phone # nvarchar X
Fax Fax # nvarchar X

PK and FK is where I need to print whether the column is Primary Key or
Foreign Key.

If CustomerId is defined as Primary Key then PK should have X printed.
Thats the objective.

How will I accomplish this ?

Thanks in advance,
AnuThe following query is close to your requirements:

SELECTc.name AS ColumnName,
p.value AS ColumnDescription,
TYPE_NAME(c.xusertype) AS Type,
CASE WHEN c.isnullable=0 THEN ' ' ELSE 'X' END AS Nullable,
CASE WHEN EXISTS (
SELECT * FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid
WHERE o.xtype='PK' AND k.id=c.id AND k.colid=c.colid
) THEN 'X' ELSE ' ' END AS PK,
CASE WHEN EXISTS (
SELECT * FROM sysforeignkeys f
WHERE c.id=f.fkeyid AND c.colid=f.fkey
) THEN 'X' ELSE ' ' END AS FK
FROM syscolumns c
LEFT JOIN sysproperties p on p.id=c.id AND p.smallid = c.colid AND
p.name='MS_Description'
WHERE c.id = OBJECT_ID('Orders')
ORDER BY c.colid

with the following observations:

1. A column is not "defined as" primary key. It is "a part of" the
primary key (see the "Order Details" table in Northwind, which has a
compound primary key). The same goes for foreign keys (it is possible
to have a compound foreign key, although this is rarely encountered).

2. The sysproperties table should be LEFT JOIN-ed to the other tables
(because if you use an INNER JOIN, the columns that do not have a
description will be omitted). We should also filter for
p.name='MS_Description' because there may be also other properties in
the sysproperties table. Moreover, it should be noted that this table
is undocumented and we should use the fn_listextendedproperty function
(because using undocumented tables may cause problems in later versions
of SQL Server).

3. Use the OBJECT_ID function instead of using directly the ID of an
object.

4. Use ANSI-style joins, where the join condition is clearly placed in
the ON clause (instead of putting it in the WHERE clause, with the
other conditions).

5. I have no idea what you will put in the "Values" column (this column
seems very odd to me).

Razvan|||Razvan, nice query. Perhaps for Anuu's value column you could have
provided the Default Value when one existed. That is the only "value"
that would seem to make any sense.

IMHO -- Mark D Powell --|||Thanks, Razvan. The query is pretty simple and delivers what I need.sql

Friday, March 9, 2012

function problem, referring to another function

I'm trying to create a couple of functions and am running into a problem
that I cannot explain. This uses Northwind database.
First of all, this one works:
---
ALTER FUNCTION fnCustomers
(
@.Initial VARCHAR(1)
)
RETURNS @.Cust TABLE
(
CustomerID VARCHAR(100)
)
AS
BEGIN
INSERT @.Cust
SELECT CustomerID from Customers where CustomerID LIKE '%' + @.INITIAL +
'%'
RETURN
END
---
and I can execute this:
SELECT * from dbo.fnCustomers('A')
and get results. Cool.
Now I want to extend this with another function that counts the results from
the first function:
---
ALTER FUNCTION fnCustomersCount
(
@.Initial VARCHAR(1)
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @.CustCount INT
SELECT @.CustCount = CustomerID
FROM fnCustomers(@.Initial)
GROUP BY CustomerID
RETURN @.CustCount
END
---
But thatdoesn't seem to work.
Select * from fnCustomersCount ('A')
gives me an error: Invalid object name 'fnCustomersCount'.
what am I doing wrong?Hello, mrmagoo
When invoking scalar UDF-s, you must use the UDF in an expression (not
in the FROM clause) and you must specify the object owner, like this:
SELECT dbo.fnCustomersCount ('A')
And by the way, why does your function return a varchar(10) instead of
an int ?
Razvan|||Try dbo.fnCustomersCount
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"mrmagoo" <-> wrote in message
news:eHB%23no%23SGHA.4900@.TK2MSFTNGP09.phx.gbl...
> I'm trying to create a couple of functions and am running into a problem
> that I cannot explain. This uses Northwind database.
> First of all, this one works:
> ---
> ALTER FUNCTION fnCustomers
> (
> @.Initial VARCHAR(1)
> )
> RETURNS @.Cust TABLE
> (
> CustomerID VARCHAR(100)
> )
> AS
> BEGIN
> INSERT @.Cust
> SELECT CustomerID from Customers where CustomerID LIKE '%' + @.INITIAL +
> '%'
> RETURN
> END
> ---
> and I can execute this:
> SELECT * from dbo.fnCustomers('A')
> and get results. Cool.
> Now I want to extend this with another function that counts the results
> from
> the first function:
> ---
> ALTER FUNCTION fnCustomersCount
> (
> @.Initial VARCHAR(1)
> )
> RETURNS VARCHAR(10)
> AS
> BEGIN
> DECLARE @.CustCount INT
> SELECT @.CustCount = CustomerID
> FROM fnCustomers(@.Initial)
> GROUP BY CustomerID
> RETURN @.CustCount
> END
> ---
> But thatdoesn't seem to work.
> Select * from fnCustomersCount ('A')
> gives me an error: Invalid object name 'fnCustomersCount'.
> what am I doing wrong?
>
>|||Thanks.
You're right. Originally it was returning an INT. I played around with it
and forgot to change it back.
Appreciate your help!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1142837800.513726.290670@.i39g2000cwa.googlegroups.com...
> Hello, mrmagoo
> When invoking scalar UDF-s, you must use the UDF in an expression (not
> in the FROM clause) and you must specify the object owner, like this:
> SELECT dbo.fnCustomersCount ('A')
> And by the way, why does your function return a varchar(10) instead of
> an int ?
> Razvan
>|||Hi
I don't think you need the second UDF. I think you would like to count the
customers , am I right?
ALTER FUNCTION fnCustomers
(
@.Initial VARCHAR(1)
)
RETURNS @.Cust TABLE
(
CustomerID VARCHAR(100)
)
AS
BEGIN
INSERT @.Cust
SELECT CustomerID from Customers where CustomerID LIKE @.INITIAL +'%'
RETURN
END
SELECT COUNT(*) FROM dbo.fnCustomers('A%')
"mrmagoo" <-> wrote in message
news:OM0BMx%23SGHA.4608@.tk2msftngp13.phx.gbl...
> Thanks.
> You're right. Originally it was returning an INT. I played around with it
> and forgot to change it back.
> Appreciate your help!
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1142837800.513726.290670@.i39g2000cwa.googlegroups.com...
>