I need a T-SQL script to find, for a given view across all databases, which ...

Question

I need a T-SQL script to find, for a given view across all databases, which ...

sgmunson

SSC Guru

Points: 110640
More actions
October 14, 2022 at 2:40 pm

#4099644

I need a T-SQL script to find, for a given view across all databases (they all have this one particular view), which ones contain a specific column and which ones don't. I've tried INFORMATION_SCHEMA, but that appears to be limited to the "current database context" in SSMS. Haven't been able to quickly find something online that solves it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Your signature line is starting to look more and more like SPAM, Steve. If I didn't know you, I'd mark your post as SPAM.

As to your question, the information you seek is available only at the individual database level. You'll need to write some code that goes through all of the databases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Thanks for the reply, Jeff... I'll just have to do this manually then, as I don't really have the time right now to write the code...

As to my signature lines, I have two links, and there are supposed to be 3 smiley emojis following the name, and as I haven't made any changes to that signature in quite some time, I can only assume that something in the website doesn't interpret the existing data the same way that it used to, so I'm going to look at it and see if I can fix the missing emojis - only one of them actually appears... as to why the ?'s appear, again, have to make the same assumption, but I will go look at it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Signature fixed to what it is supposed to be.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

sgmunson wrote:

Thanks for the reply, Jeff... I'll just have to do this manually then, as I don't really have the time right now to write the code...
As to my signature lines, I have two links, and there are supposed to be 3 smiley emojis following the name, and as I haven't made any changes to that signature in quite some time, I can only assume that something in the website doesn't interpret the existing data the same way that it used to, so I'm going to look at it and see if I can fix the missing emojis - only one of them actually appears... as to why the ?'s appear, again, have to make the same assumption, but I will go look at it.

Understood that you've not change it in a while. I'm just making a suggestion because it's been like that for a while. 😉 😉 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

p.s. You could do a "quickie" with sys.sp_MSforeachdb . It's not something that I'd use for production code but it works for a "one off".

https://www.kodyaz.com/articles/sql-server-exec-sp_msforeachdb-sp_msforeachtable-example-tsql-code.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Turns out I was chasing an OLD problem... so it was kind of moot once I realized what was going on... Our SSIS package had been ignoring a column entirely, and apparently, that is okie dokie, so the problem no longer exists.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Oh.. and I don't post anywhere near as often as I used to... and it's cause I'm a lot busier than I've ever been, so I hadn't even noticed that the signature was off, and even if I had, it probably looked more like a website issue to me than a signature problem precisely because I had remembered putting exactly 3 smiley emojis in it, without any question marks... and thus I probably mentally dismissed it and moved on...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

Heh... ya kinda missed my point.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

sgmunson wrote:

I need a T-SQL script to find, for a given view across all databases (they all have this one particular view), which ones contain a specific column and which ones don't. I've tried INFORMATION_SCHEMA, but that appears to be limited to the "current database context" in SSMS. Haven't been able to quickly find something online that solves it...

Here is an answer to the question, just added it for completeness 😉

😎

To query different databases' schema information on the same server, one simply has to use 3 part identifiers, [DATABASE].[SCHEMA].[OBJECT]

USE master;
GO
SET NOCOUNT ON;
GO

SELECT * FROM master.INFORMATION_SCHEMA.COLUMNS     UNION ALL
SELECT * FROM model.INFORMATION_SCHEMA.COLUMNS      UNION ALL
SELECT * FROM msdb.INFORMATION_SCHEMA.COLUMNS       UNION ALL
SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS

Obviously, the user must have access to all the databases.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

sgmunson wrote:

Haven't been able to quickly find something online that solves it...

30 second search https://duckduckgo.com/?q=find+column+name+in+all+databases+sql+server led to the following:

https://sqlserverplanet.com/dba/find-column-in-all-databases

Hope it helps.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

Jeff Moden wrote:

sgmunson wrote:
Haven't been able to quickly find something online that solves it...
30 second search https://duckduckgo.com/?q=find+column+name+in+all+databases+sql+server led to the following:
https://sqlserverplanet.com/dba/find-column-in-all-databases
Hope it helps.

The sp_MSForEachDB procedure is not on my menu, 5 dynamic T-SQL code executions using EXEC, handful of cursors 🙁

😎

Like so much of the old system code, most of what I have reviewed in details, is so bad that I include the names in my static code analysis as a risk on any production system.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

Totally understood and, I never thought I'd say this, but this is for a "one-off". I'll invest some time in a better one later. And, I totally agree with the bad "system code" mention. sp_SpaceUsed was one of my favorite examples of how to not write code. They've improved it quite a bit over the last few releases but it could still use a bit of help. To your point, code like sp_MSforeachdb still makes major sucking sounds for the reasons you mention and more. I'd never use it for production code.

What ticks me off about this whole fire-drill is that the old "Enterprise Manager" product would do such a thing just by pressing the {F4} key. Also, the new "version 17 and later" of SSMS has a huge number of regression issues. For example, (a pet peeve for when creating articles and presentations), custom colors are no longer followed when using Copy'n'Paste like they were prior to the "improved" version 17. And there's no documentation on the "version" of RegEx they've decided to use. They do point you to an article about RegEx but half of it doesn't seem to work in SSMS (and I'm being kind there).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 13

Here is some code I had pirated about 6 years ago:

CREATE TABLE #temp_list

(

db_names varchar(500),

tbl_names varchar(500)

)

;

EXEC sp_msforeachdb 'INSERT INTO #temp_list SELECT "?" AS db_names, name AS tbl_name FROM [?].sys.tables'

;

--SELECT COUNT(*)

--FROM #temp_list

--;

SELECT *

FROM #temp_list

WHERE tbl_names = 'LobXFORM'

-- WHERE tbl_names LIKE '%LobXFOR%' /* Use if you don't know the exact name */

;

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.