How to use grep and filter to filter specific tables with parents and children?

[aisbergde]

From the katacode examples I understand, that I should use grep and filter to filter out some objects with their "parents" and "children". This is exactly, what I need. But I don't get it realized.

First I used limit to filter one database (dhw_self) and one specific table in this database, this works
it doesn't work when I omit the limit --schemas=dhw_self\..*

sweep
connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --user=readonly --password=readonly
limit --schemas=dhw_self\..*
limit --tables=(?i)dhw_self\.repo\.generatorusp
load --info-level=standard
execute --command=list

I also tried to use some schema pattern instead of the exact database name, but this doesn't work, the result is empty

limit --tables=(?i)\w*\.repo\.generatorusp
load --info-level=standard
execute --command=list

Now I want to use filter to show some parents or children
My understanding: filter needs to be used with grep
So first I need grep to get work:

BTW, I don't understand the output which is generated in katacoda example
SchemaCrawler - Introduction to the Interactive Shell
Step 5

grep --grep-columns=(?i).*data
this should filter for columns ending on data, but the result is something different. There are tables listed, not containing columns ending in data

So I am very confused about the usage of grep and the help grep doesn't clarify this for me.

I also had a look on file:///C:/ProgramData/chocolatey/lib/schemacrawler/schemacrawler-16.14.1-distribution/examples/grep/grep-readme.html
but it doesn't help me

Back to my goal:
There is no grep --grep-tables
That's why my understanding is, I need to use grep --grep-columns to filter a specific table
I try this, to filter the table dhw_self.repo.RepoObject

sweep
connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --user=readonly --password=readonly
limit --schemas=dhw_self\..*
load --info-level=standard
grep --grep-columns=dhw_self\.repo\.RepoObject\..*
execute --command=list

and this

sweep
connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --user=readonly --password=readonly
limit --schemas=dhw_self\..*
load --info-level=standard
grep --grep-columns=(?i)dhw_self\.repo\.repoobject\..*
execute --command=list

Because I think I need to define the whole qualified name for the table and at the end \..* for .AnyColumnName

But this doesn't work, I get all objects of the database, grep doesn't filter anything. I get the same result which I get without grep

• Why the katakoda example for grep is resulting in the result we see?
• What is wrong with my grep statements?
• What is the right way to filter / grep one dedicated object with a number of parents and children?

[aisbergde]

I would like to explain why a solution to this question is important to me:

For a long time I have been looking for a way to represent real and virtual relationships between objects or object columns together also interactively and graphically, preferably as ER diagrams, with "parents" and "children".

Since I am a database designer and not an application developer, I am looking for solutions without having to learn intensively new programming languages besides SQL.

In my search I also found SchemaCrawler. With Katacoda I could get an overview, and in "SchemaCrawler - Introduction to the Interactive Shell" step 7, I found

So Include Referencing Tables
From the SchemaCrawler Interactive Shell, run:
filter --parents=1

So that's exactly what I'm looking for! Only I'm looking for this for a combination of real and virtual relations. And with the possibility to use different sets of virtual relations. Hence, then my first request #407

And that was even implemented, so now I had every reason to test SchemaCrawler with my own databases. The start was not easy, but solutions were found #410

Now to be able to use SC as planned, i.e. with filter, it is my understanding that you also have to use grep. And therefore I have to find out how it is possible with grep to "grep" exactly one specific object (or a set of objects) to then use filter. If I can't do that, then I can't use SC as intended.

That's why this post here.

[sualeh]

I will reply later. Let me digest this.

[aisbergde]

If I can't do this with grep and filter, surely other things will work. I could then use limit, define the list of needed objects outside the SC and send the finished list of tables to the SC. So in principle this is possible. But would be so tempting that there is a filter, which would do this job for me! But it's not a showstopper if that doesn't work with filter. Not that I am misunderstood.

[aisbergde]

Would it be possible to use values in extended properties to filter objects?

In my database each object has an extended_property "RepoObject_guid" with a guid (uniqueidentifier) as value. Would it be possible to use the contents of these extended_properties to pass to SC a list of objects to filter for? Or is it only possible to work with limit and a list of fully qualified object names?

I already have functions that give me this list of RepoObject_guid.

If that doesn't work, I can also translate it to full qualified regex. But maybe it already works to use the content of extended properties directly. I'm afraid that there might be problems with the interpretation of regex with full qualified names.

[aisbergde]

I found, that I should use "" to bring the parameter context to the parameter. So this works, when I will prepare the list myself. I hope, the content of the parameter is not too limited, and I can also include 20 or 50 tables this way :-)

schemacrawler --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --schemas=dhw_self\..* --tables="(?i)(dhw_self\.repo\.generatorusp)|(dhw_self\.repo\.generatoruspparameter)|(dhw_self\.repo\.generatoruspstep)" --info-level=standard --command=list --user=readonly --password=readonly

But this is a solution not using the grep and filter.

[sualeh]

@aisbergde - that is strange behavior. Let me see what is going on in SchemaCrawler.

[sualeh]

@aisbergde - I took a close look at what you are doing. load should done after limit, grep and filter.

sweep
connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --user=readonly --password=readonly
limit --schemas=dhw_self\..*
grep --grep-columns=(?i)dhw_self\.repo\.repoobject\..*
load --info-level=standard
execute --command=list

[sualeh]

@aisbergde - please read through my earlier notes again.

[aisbergde]

@sualeh Your are right, in this notes your describe this. Also in the Katacoda example the load is called after grep and filter.
But this note in #426 is the only place where this is documented. It is not contained here https://www.schemacrawler.com/schemacrawler-shell.html or in the online help.

Maybe the documentation could be extended to avoid misusage?

• why to use sweep (to change connection or limit?)
• how to use filter (after and only afte grep?)
• the right order: connect, [limit], [grep], [filter], load, execute

My misuse was based on the false assumption that the data is loaded first, and then you can filter the loaded data afterwards. Now in hindsight I realize that this is not how it works. But when I tested shell, the remark from #426 was not present in my brain, not available in the documentation, and I was blind to the Katacoda example. Those are my mistakes, no question.

[sualeh]

I have already added the documentation to https://www.schemacrawler.com/schemacrawler-shell.html and it will be pushed out with the next SchemaCrawler release. Please take a look to see what the documentation looks like - here is the change

[sualeh]

@aisbergde - did you solve your issue with filtering on specific tables with parents and children?

[aisbergde]

@sualeh - no, I can't find, how to filter a specific table using grep. The rexeg doesn't do what I want it to do, normally I get too many objects: #439

maybe, the not existing grep-tables could do a better job, then grep-columns

But it looks like I can use limit --tables. In this case I think I can't use grep and filter (because it is already filtered by table?, I did not yet test), but I would need to prepare the list of parents and children myself. If not working interactively, this could be OK. For example, I could prepare the set of related object in the database to generate diagrams for all objects with one parent and one child in batch.

Of course it would be fine if I could find a way to grep exactly one object by name and then to filter interactively. The goal was to interactively grep one or more objects (tables, views) and then to evaluate more or less parents or children interactively.

[sualeh]

@aisbergde - for some reason, you need to use \\ where you would normally use \. I am trying to figure out why. So, here are some examples in SchemaCrawler interactive shell:

sweep
connect --server offline --database dhw_self.ser 
limit --schemas dhw_self\\.*
grep --grep-columns dhw_self\\.repo\\.RepoObject\\..*
load --info-level standard 
execute --command list --no-info

sweep
connect --server offline --database dhw_self.ser 
limit --schemas dhw_self\\.*
grep --grep-columns dhw_self\\.repo\\.RepoObject\\..*
filter --parents=1 --children=1
load --info-level standard 
execute --command list --no-info

sweep
connect --server offline --database dhw_self.ser 
limit --tables=(?i)dhw_self\\.repo\\.generatorusp
load --info-level=standard
execute --command=list

Please let me know if this works for you. You can also connect using
connect --url=jdbc:sqlserver://localhost;instancename=sql2019;databaseName=dhw_self; --user=readonly --password=readonly

[sualeh]

@aisbergde - I have solved the issue of "" not being taken into account in regular expressions. This will be available in the next SchemaCrawler release. I will be closing your last issue, #427.

This leaves just one problem: why do you need to limit to --schemas=dhw_self\.* the first time you run the SchemaCrawler shell. For this issue, I will need to connect to your Azure database when you set it up.

[aisbergde]

Not sure, if we can reproduce the issue on an Azure database. It works a bit different than a server. And I would need to create a new Azure account first to get free database for 12 month. Before doing this I will first try to reproduce the issue on another SQL Server.

Another way would be to learn how to work with docker. Then we could use and share the same docker image. Then it is easier to reproduce.

I had been planning to get involved with docker for a while. Maybe now is a good time. Please give me a few days: for testing on another server and for docker.

[sualeh]

@aisbergde - ok. In the meantime, I will release a new SchemaCrawler version, which will have a lot of enhancements that you need: better documentation on the website and in help, ability for the Shell to take "\", a new --grep-tables option, and support for alternate keys.

[aisbergde]

I get a similar result on other servers with multiple databases. And it seems logical to me: if there are 10 or 20 or 50 or 100 databases on a server, then you can't expect that all objects of all databases will be read. So the "Connection" only establishes a connection to the server, and then maybe 150 - 250 objects come there, in some order.

But if schemas is actually used to restrict to a concrete database, then the driver can (and will) supply all objects, and not only a small sample. So this seems very logical to me that you absolutely have to "limit" a concrete database as well.

I think if I can replicate that on different servers, you can too. And I don't think that's an issue, I think that's the way you have to work with SQL server.

[aisbergde]

BTW, that's also different to Azure Database: Azure Database is only one database, and it could be possible, that you get all objects. I did not test, because currently I have no Azure database and first would need to setup one.

[sualeh]

@aisbergde - please use SchemaCrawler 16.14.2
See https://www.schemacrawler.com/schemacrawler-shell.html