An efficient method for retrieving metadata of MS SQL Server databases

dev
1

Hi,

I’d like to retrieve meta data of database(s) under a particular ms sql server. Right now I’m attempting to do so from sql client CLI call with cfexecute. I wonder if you have a better idea. thanks.

edited below:

Ok guys, my bad. Re-stating the question below.

an MS SQL Server may have several to many databases, one may have a need to know all the metadata (database names, respective tables and their columns), and one may not have the luxury of setting up cf/lucee datasources for each, hence, using cfexecute calling a batch script that launching a SQL client to connect to the given SQL server to retrieve such metadata and output results for lucee to further process seems an option. I’ve got such a proof-of-concept working already but I’m not satisfied with it yet. Thanks.

2

what sort of metadata? you can always just query the system tables or call any of the stored procs

i.e.

SELECT	fk.name 'FK_Name', tp.name 'Parent_table',
        cp.name, cp.column_id, tr.name 'Referenced_table', cr.name, cr.column_id
FROM	sys.foreign_keys fk
        INNER JOIN sys.tables tp
            ON fk.parent_object_id = tp.object_id
        INNER JOIN sys.tables tr
            ON fk.referenced_object_id = tr.object_id
        INNER JOIN sys.foreign_key_columns fkc
            ON fkc.constraint_object_id = fk.object_id
        INNER JOIN sys.columns cp
            ON fkc.parent_column_id = cp.column_id
                AND fkc.parent_object_id = cp.object_id
        INNER JOIN sys.columns cr
            ON fkc.referenced_column_id = cr.column_id
                AND fkc.referenced_object_id = cr.object_id
ORDER BY tp.name, cp.column_id
3

While it’s not what I was asking, such sql stmt might be handy for others.

4

Ok, I’ll just repeat myself, what sort of metadata?

Detailed questions get better answers!

5

Maybe cfdbinfo could help ?

Again. You don’t say what info you want so I am just taking a wild stab in the dark.

6

You didn’t actually ask a question, soooo.

Please state your question in the form of a question!

And provide maybe

  1. what cfexecute call you’re actually doing

  2. What metadata you’re looking for (DATABASE metadata? Tables? Columns? Datatypes? Foreign Keys? Check constraints? Stored Procedures? Definitions of Views or Procedures?)

I’m with Zach, the answer with MSSQL if you want to roll your own is going to be the sys schema. That’s where EVERYTHING is. (everything listed in 2 and more)

I’m also with Mark in that if you want something packaged by CF and easily consumable, cfdbinfo is the way to go.

1 Like
7

please see re-stated question, tks

8

You can get all that info by querying the DB, can you just share what are you cf-executing?

9

It’s possible you misconstrued my re-statement of the question, you keep on saying “querying the DB” – implying just one database but it’s several to many databases. appreciate the effort tho.

10

You should still be able to query the db…

select
		[Name]
	from
		[sys].[databases]

Will give you all the databases on the server (assuming you connect with appropriate rights)

select
				[Table_Name] as [Table]
			from
				[Information_Schema].[TABLES]
			where
				[Table_Type] = 'BASE TABLE'
			order by
				[Table]

WIll give you the tables

select
				[Column_Name] as [Column],
				'[' + [Column_Name] + ']' as [ColumnWrapped],
				[Data_Type] as [Datatype],
				[Is_Nullable] as [allowNulls]
			from
				[Information_Schema].[Columns]
			where
				[Table_Name] = 'TABLE_NAME_HERE'
			order by
				[Table_Name]

Will give you the fields…

11

If your datasource’s credential has appropriate rights, you can do all that from Lucee.

Do a cfquery like this

SELECT * FROM master.sys.databases WHERE is_read_only=0 and is_in_standby=0

You can loop over the results and pull tables from all, i.e.

SELECT <cfqueryparam value="#dbName#"/> as DBName, * FROM [#dbName#].sys.tables

You could further concatenate each of those queries together.

Or you can use the datasource and override parts of it, like the username and password…

One of the mgmt jobs I have here loops over all databases (300+) on my production cluster and gets usage stats and aggregates them in another table. I have a datasource with no username or password defined for each server - i.e. PRODSQLV01, PRODSQLV02, with the server hostname, port, and master as the database, blank username and password. (Because the tool asks for the credentials of a db user who has sufficient rights to do this)

Then I read my database of DB Names, Source Servers, Usernames and passwords (encrypted), loop over the results and do something like:

<cfquery datasource="#sourceserver#" username="#usercred#" password="#password#">
  SELECT <stuff> FROM [#dbname#].dbo.Table1 A inner join [#dbname#].dbo.Table2 ......
</cfquery>

Get my numbers and insert into my Agg database.

Note when using 3 dot syntax you CANNOT put dbname in a queryparam - then you’d be telling SQL that it’s a variable, NOT an object name. Yes, this does open you up to SQL injection so be sure your db name variable actually contains a database name, and do whatever you can to make sure it can’t be abused.

-G

1 Like
12

For that matter there’s nothing restricting you from cross-DB joins and other such tomfoolery. :slight_smile:

13

sorry, I want to avoid cf datasource for this project and that’s why cfexecute a batch script to run a sql client cmd to get db metadata.

14

You can have one data source to master that will give you that. You don’t need to connect to each.

15

You initially asked for a better approach, several people took the time to provide you with some advice and now you don’t want it?

Next time you ask, do you want people to waste their time?

16

Take it easy, Zac, probably initially my question were not well formed, I always appreciate your input.