[RESOLVED] Where Is My Metadata?

[Shaggy Hiker]

I wrote something to update the extended properties metadata for a table in SQL Server (I forget the version, but something like 2017). To do this, I call the sp_AddExtendedProperty
with these parameters:

            cmd.Parameters("@name").Value = "OWNERS"
            cmd.Parameters("@value").Value = ownString
            cmd.Parameters("@level0type").Value = "SCHEMA"
            cmd.Parameters("@level0name").Value = [Enum].GetName(GetType(HISInCommon.Schema), tableHold.DBSchema)
            cmd.Parameters("@level1type").Value = "TABLE"
            cmd.Parameters("@level1name").Value = tableHold.TableNameBase

Now, this all works. I know it works because these often don't change (the metadata very rarely changes), so the first thing I do is read any existing metadata and compare it to what it should be, updating as needed. By doing this, and stepping through the code, I see that the metadata is correctly being stored. The thing is, I can't find it.

My understanding was that I could go into SSMS, look at the Properties for a table, then look at Extended Properties, and there it would be, with the name field holding OWNERS, and the value field holding whatever is in ownString. However, when I go and look there...it's empty. There are no extended properties at all, even though when I get the OWNERS property back from the table, I get back exactly what I am expecting.

So, it's being stored somewhere, but where? Am I doing it wrong? It's working, so it can't be all THAT wrong, but it should be visible and it is not.

[Zvoni]

Sys.objects/Sys.columns?

https://stackoverflow.com/questions/...eir-data-types
https://docs.microsoft.com/en-us/sql...l-server-ver15

btw: Why not using INFORMATION_SCHEMA?

[Shaggy Hiker]

I hadn't encountered INFORMATION_SCHEMA, so I'll have to look into it.

Note that the problem isn't the functionality, exactly. I can write, and read back, what I want to. In that regard, it is working the way I want it to. It's just that my understanding was that I would be able to see the information using SSMS in the table properties, rather than requiring a query. The query approach works fine with the approach I've taken, it's just that the data isn't showing up in the table properties the way I expected it to.

[Zvoni]

Have you tried it the other way round?
use this property thingy in SSMS, and then go hunting through the tables, schemas etc.?
just enter some bogus-data which should be „easy“ to find

[wqweto]

So, it's being stored somewhere, but where?

Try this

SELECT      *
FROM        ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, NULL, NULL)

This should list all extended properties of all tables.

Also this

SELECT      *
FROM        ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', '<<MyTable>>', 'column', NULL)

. . . lists value of Description key from Column Properties attributes grid in SSMS table designer for <<MyTable>> table.

Btw, don't use INFORMATION_SCHEMA views unless you aim portability. These are implemented dog slow (with multiple joins) compared to sys.tables and sys.columns tables (technically views) or even the somewhat faster system functions like OBJECT_ID or COL_LENGTH (can be used to test for object/column existence).

cheers,
</wqw>

[Shaggy Hiker]

Good to know about that INFORMATION_SCHEMA. I'll try the function. It would be very interesting if it doesn't return what I'm expecting, though, since the information IS there. Still, it would be a good test of an assumption, and that's often quite valuable.

[Shaggy Hiker]

Have you tried it the other way round?
use this property thingy in SSMS, and then go hunting through the tables, schemas etc.?
just enter some bogus-data which should be „easy“ to find

No, I haven't, and that's a good idea. I also haven't tried it with the key "MS_Description", which appears to be somewhat special. I ran out of time, posted the question, and won't be able to get back to that until later on this week. I'll have to try that, though.

[OptionBase1]

Have you tried it the other way round?
use this property thingy in SSMS, and then go hunting through the tables, schemas etc.?
just enter some bogus-data which should be „easy“ to find

Back when I was an IT intern while in college I came up with what I called "poop troubleshooting". Basically, if you were trying to track down where something was stored (most often in the registry), give it a value of "poop" and then search for poop. The theory was that "poop" almost certainly isn't present for other reasons, so the frequency of false positive results should essentially be 0. Perhaps a bit crass, but useful nonetheless.

[Shaggy Hiker]

I work in biology. Poop is everywhere.

Still, it's good reasoning.

[techgnome]

Back when I was an IT intern while in college I came up with what I called "poop troubleshooting". Basically, if you were trying to track down where something was stored (most often in the registry), give it a value of "poop" and then search for poop. The theory was that "poop" almost certainly isn't present for other reasons, so the frequency of false positive results should essentially be 0. Perhaps a bit crass, but useful nonetheless.

The FUBAR test...

-tg

[Shaggy Hiker]

Well, THAT was annoying.

As it turns out, I was looking at the wrong database. I had forgotten that I had two different versions of SQL Server on that computer. I thought I had removed the older version, but I hadn't. The query was doing exactly what I thought it should be doing. The extended properties were where I thought they would be, but I was looking at the wrong database.

In the end, it was Zvoni's suggestion that tipped me off. I was able to see that the dummy value I put in place was, in fact, right where I thought it should be, but that the one I was looking for was not. That got me thinking about whether I was looking at the right database, which I hadn't considered because I thought there was only one.