How to use and examples missing in your nice docs and help

Part of the SQLFacts suite of tools
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

How to use and examples missing in your nice docs and help

Post by eliassal »

Hi, frist, I would like to thank you for making this tool available for the community and decided to give it a go. I went through readme and other text files, everything is well explained but no chapter indicates the minimum "How To" to get started especially for SQLFacts.exe. Can you please let me know how to run and where, on commandline, powershell, inside SSMS in cmd mode.......
It would be very interesting that you add some use cases and graphs to your help files to jump start.
Thanks again
Wingenious
Site Admin
Posts: 5
Joined: Sun Jun 13, 2021 5:27 am

Re: How to use and examples missing in your nice docs and help

Post by Wingenious »

Thanks for the feedback!

You make some very good points. We will work on additions to the documentation, especially a Getting Started section.

All 26 tools in the suite are purely T-SQL code that you can run in SSMS and review the result set(s).

If you run the SQLFacts tool (SQLFacts.sql) in SSMS you will get 48 result sets. It's a lot to go through! It's why we created the SQLFacts.exe component, which is completely optional to use. The SQLFacts.exe component does one very simple thing. It runs the SQLFacts.sql code in the context of the connection/database you specify and exports the 48 result sets to HTML files (and CSV files), along with a table of contents HTML file (as a menu). The HTML files are a nice way to generate easily shared documentation for your database.
Wingenious
Site Admin
Posts: 5
Joined: Sun Jun 13, 2021 5:27 am

Re: How to use and examples missing in your nice docs and help

Post by Wingenious »

We added a Getting Started section to the README file. It includes a few use cases.
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

Re: How to use and examples missing in your nice docs and help

Post by eliassal »

Thanks, appreciate your efforts for the community
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

Re: How to use and examples missing in your nice docs and help

Post by eliassal »

Can you please tell me how can we use Browse and Search functions, can you give an example of how we can search for a word "Customers" as a table?
Thanks
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

Re: How to use and examples missing in your nice docs and help

Post by eliassal »

Please ignore my last post as I found that a string should be entered on line 32
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

Re: How to use and examples missing in your nice docs and help

Post by eliassal »

For my understanding, in Browse functions, we should give the exact schema and table name. What I mean is that if I have a table in schema other than dbo,, like myschema.Tip then the 2 lines in the beginning

1- first execution

Code: Select all

DECLARE @GeneralSchema varchar(0128) = 'dbo' -- enter schema name here
DECLARE @GeneralObject varchar(0128) = 'Tip' -- enter object name here
2 - 2nd execution
won't work. I need to do
DECLARE @GeneralSchema varchar(0128) = 'myschema' to make it work

2nd, when I uncomment

INSERT @Match ([Schema]) VALUES ('dbo') , ('myschema')

first, I have in the @Match table duplicate lines for dbo and myschema and the results are not identitical to 2nd execution where I have 2 more resultsets
Wingenious
Site Admin
Posts: 5
Joined: Sun Jun 13, 2021 5:27 am

Re: How to use and examples missing in your nice docs and help

Post by Wingenious »

Yes, in the Browse tool, you should enter the schema name and object name of the table (or other types of items) in question.

There are two statements for INSERT @Match. The first one (disabled by default) is for explicitly naming the schemas to examine. The second one (enabled by default) is for examining dbo and all custom schemas. You should use one or the other, but not both. If you use both then you will have two rows for the dbo schema, as you noted, although the duplicate row does not really matter given how the @Match table variable is used in the code.

I'm not sure what you mean by "2 more resultsets". If the object is a table there are 12 result sets. If the object is a routine there are five result sets. There could be a differing number of the result sets populated, depending upon how your schema limitations (@Match) were done. For example, if you use the first INSERT @Match block and do not name myschema then related objects in myschema will not be included in the results.
eliassal
Posts: 7
Joined: Fri Nov 19, 2021 8:07 am

Re: How to use and examples missing in your nice docs and help

Post by eliassal »

Ok, thanks.

When I use myschema & table name,

DECLARE @GeneralSchema varchar(0128) = 'mssqltips' -- enter schema name here
DECLARE @GeneralObject varchar(0128) = 'Tip' -- enter object name here

yes I get 12 resultsets but 9 out of them are empty as you can notice in the following snapshot

Image

But when I do

DECLARE @GeneralSchema varchar(0128) = 'dbo' -- enter schema name here
DECLARE @GeneralObject varchar(0128) = 'Tip' -- enter object name here

and at the same time I uncomment

Code: Select all

INSERT @Match ([Schema]) VALUES ('mssqltips')
and comment the other INSERT

Code: Select all

/*
   INSERT @Match ([Schema])
   SELECT S.name
     FROM sys.schemas AS S
    WHERE CASE WHEN S.schema_id  =     1 THEN 1
               WHEN S.schema_id  =     2 THEN 0
               WHEN S.schema_id  =     3 THEN 0
               WHEN S.schema_id  =     4 THEN 0
               WHEN S.schema_id !< 16384 THEN 0 ELSE 1 END != 0
 ORDER BY S.schema_id
 */
I get 6 empty tables and no details about "Tip" table

Snapshot 1
Image

Snapshot 2
Image
Wingenious
Site Admin
Posts: 5
Joined: Sun Jun 13, 2021 5:27 am

Re: How to use and examples missing in your nice docs and help

Post by Wingenious »

Please start with an unmodified copy of the Browse tool.

Set @GeneralSchema to "mssqltips", or whatever the schema name actually is.

Set @GeneralObject to "Tip", or whatever the object name actually is.

Execute the SQL code.

If you have a table as mssqltips.Tip you will get 12 result sets. It's very possible that some of the result sets will be empty. For example, if you have no partitions then those result sets will be empty. Further, if you change how @Match is populated and inadvertently exclude the schema(s) where related objects live then those related objects will not show up in the result sets. The default is for @Match to be populated with "dbo" and all the custom schemas in your database.

You may need to double check the result set count for your other example. I think you are getting five empty result sets, not six. You will get five empty result sets if your database does not contain an object as mssqltips.Tip (or whatever you specify). I could/should change the behavior in this case, but the tool is currently assuming an existing object is specified.

The point of the tool is to provide details about the object you specify, and any related objects. If the object you specify does not exist, the tool cannot provide any details about it. If you inadvertently (or intentionally) exclude the schema(s) where related objects live, the tool does not (and cannot) provide any details about them.
Post Reply