Querying the INFORMATION_SCHEMA

Question

Post reply

Querying the INFORMATION_SCHEMA

Viewing 10 posts - 16 through 24 (of 24 total)

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

looks like you haven't applied SP2 to your client tools

Microsoft SQL Server Management Studio9.00.3042.00

Microsoft Analysis Services Client Tools2005.090.3042.00

Microsoft Data Access Components (MDAC)2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer7.0.5730.13

Microsoft .NET Framework2.0.50727.1433

Operating System5.1.2600

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 2

I have other similar macro scripts that work in SQA and not SSMS. It has been really frustrating not to be able to use them effectively in SSMS - especially if you are a type and typo centric TSQL'r.

I will apply SP2 and see if it works. Thanks for the posts and the insight re: SP2.

-Mike

Wyatt Winghead Old Hand Points: 316 More actions · Answer 3

Wyatt Winghead

Old Hand

Points: 316

March 11, 2008 at 8:50 am

#788036

That does the trick!

Thanks!

Andrew_Webster SSChasing Mays Points: 630 More actions · Answer 4

Top tip for working with the INFORMATION_SCHEMA views: use Excel to view them.

In Excel, go to Data, Import External Data..., then either Import Data or New Database Query. Use or create a DSN and fish out the INFORMATION_SCHEMA view that your interested in. COLUMNS is the one I use the most.

Then use Excel's Autofilter and you got yourself a Data Dictionary that you can filter to find, say, all fields with the same name, all fields of the same datatype (use char instead of nchar and have a feeling you've done it somewhere else too?), or whatever.

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

Andrew_Webster (3/18/2008)
Top tip for working with the INFORMATION_SCHEMA views: use Excel to view them.
In Excel, go to Data, Import External Data..., then either Import Data or New Database Query. Use or create a DSN and fish out the INFORMATION_SCHEMA view that your interested in. COLUMNS is the one I use the most.
Then use Excel's Autofilter and you got yourself a Data Dictionary that you can filter to find, say, all fields with the same name, all fields of the same datatype (use char instead of nchar and have a feeling you've done it somewhere else too?), or whatever.

Hadn't thought about that... that's a very clever and useful idea! Thanks, Andrew!

--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)

Timothy Lenz SSC Enthusiast Points: 196 More actions · Answer 6

Hi Mike,

I enjoyed the article that I found as I was searching for something similar. We are trying to clean up a database where the dba stored every ad hoc report he was asked to create as a stored procedure. I was hoping to find a way to query the INFORMATION_SCHEMA to see what stored procedures had not been used in the last month or more and be able to target them as possible candidates for deletion. Is that possible?

Thanks,

Tim "DBA jr."

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 7

Tim:

Sorry for the late response. Hopefully by now you have found the solution. The question does raise an eyebrow. Basically, when was the last time a given sproc was fired. I know there are thrid party tools that monitor a database for activity - a table structure was modified, a sprc changed, etc. I do not have an immediate answer.

adrian_rf SSC Veteran Points: 275 More actions · Answer 8

Hi Timothy,

monitoring 'execution count' for a while might help.

See this article by Gregory Larsen -

http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

dalexmailbox Old Hand Points: 378 More actions · Answer 9

INFORMATION_SCHEMA.ROUTINES does not return triggers data, look here:

ROUTINE_TYPE
nvarchar(20)
Returns PROCEDURE for stored procedures, and FUNCTION for functions.

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 10

WOW

I have not seen this article in a long time. IT is pretty cool to see that it is still out there.