[MSSQL] Alle 'user' stored procedures opvragen

[Verwijderd]

Goed, mijn eerste post hier, laat ik eens proberen om het in een keer goed te doen.

Case:
Ik heb voor mijn stage een enorme database om mee te werken van enkele jaren oud. Groot probleem is dat er weinig tot niets gedocumenteerd is/wordt (ik ben er hard om aan het zeuren, en ik geloof dat 't nog effect heeft ook), dus er is niet bekend welke objecten niet meer gebruikt worden. Wat ik nu wil doen is door alle code van de applicaties en alle procedures heen lopen om de ongebruikte objecten te localiseren.

Mogelijke oplossing 1:
ApexSQL Clean (http://www.apexsql.com). Een programma dat dit alles al doet als het goed is. Een klein probleempje... Het is shareware, en dus niet zomaar te gebruiken in een productieomgeving (tenzij het gekocht wordt).

Mogelijke oplossing 2:
Ik ga dus zelf aan de slag om iets dergelijks te fabriceren. Natuurlijk zal dit een minder gelikt product opleveren dan het tooltje van ApexSQL, maar wel compleet in eigen beheer, met de features die gewenst zijn.

Case:
Ik heb via google een hoop gezocht, en ben achter de volgende dingen gekomen:

1. in de tabel sysobjects zijn alle items waarbij xtype = 'P' procedures.
2. in de tabel syscomments staat in het veld 'text' de echte inhoud van de stored procedure; de sql zelf dus.

Wat ik nu alleen nog zou willen kunnen voor het mooi, is om een onderscheid te maken tussen user en system procedures, zoals de enterprise manager dat zo mooi doet. Nou vraag ik me af, is dat ergens terug te vinden, of zit dat hardcoded in de enterprise manager (zoals dtproperties ook eigenlijk een user object is blijkbaar)?

Alvast bedankt.

[Verwijderd]

Verwijderd schreef op vrijdag 17 juni 2005 @ 16:49:
Wat ik nu alleen nog zou willen kunnen voor het mooi, is om een onderscheid te maken tussen user en system procedures, zoals de enterprise manager dat zo mooi doet. Nou vraag ik me af, is dat ergens terug te vinden, of zit dat hardcoded in de enterprise manager (zoals dtproperties ook eigenlijk een user object is blijkbaar)?

Bij de inloggegevens van de Enterprise manager kun je aangeven of je ook de system procedure, tabellen, e.d. wilt zien. Dit zou je in de connectiestring moeten kunnen regelen. (pin me er niet op vast).
Maar hoe je dan het onderscheid kunt maken tussen de verschillende objecten?

[Verwijderd]

Ja, precies, in de enterprise manager kan je system tabellen filteren. Maar ook als je dat niet doet, wordt er aangegeven wat system en wat user objecten zijn in de lijsten. Aangezien volgens mij zo'n beetje alles wel in die system tabellen staat moet er toch ook op de een of andere manier het type (system of user, is er nog een ander type) uitgehaald kunnen worden... TENZIJ de enterprise manager gewoon een vooraf gedefinieerde lijst gebruikt. Ik hoop 't niet, want dat is niet netjes

[whoami]

Als je eens kijkt wat de system-procedure sp_MS_marksystemobject doet (deze procedure set een vlag om te definieren dat iets een system object is of niet), dan zie je dat die procedure het 'status' veld van het betreffende object aanpast. Er wordt blijkbaar een vlag gezet die de hex waarde c0000000 heeft.

Je wilt dus de objecten krijgen die deze vlag niet hebben (dan verkrijg je de user defined procedures):
Als je dus deze query doet:

code:

SELECT * 
FROM sysobjects
WHERE xtype = 'P'
AND status & 0xC0000000 > 0

Dan zou je de user defined SP's moeten krijgen.

[ Voor 10% gewijzigd door whoami op 17-06-2005 18:28 ]

[Verwijderd]

whoami schreef op vrijdag 17 juni 2005 @ 18:25:
Als je eens kijkt wat de system-procedure sp_MS_marksystemobject doet (deze procedure set een vlag om te definieren dat iets een system object is of niet), dan zie je dat die procedure het 'status' veld van het betreffende object aanpast. Er wordt blijkbaar een vlag gezet die de hex waarde c0000000 heeft.

Als je dus deze query doet:

code:

SELECT * 
FROM sysobjects
WHERE xtype = 'P'
AND status & 0xC0000000 > 0

Dan zou je de user defined SP's moeten krijgen.

Dit klopt inderdaad, gelukkig!
één dingetje... Jouw voorbeeld geeft niet de user defined SP's terug, maar de system SP's... In theorie. Het veld status is van het type int, wat 0xC0000000 een negatief getal maakt. Dus om alle user defined stored procedures te vinden gebruik ik nu

code:

SELECT *
FROM sysobjects
WHERE sysobjects.xtype = 'P'
AND (status & 0xC0000000) = 0

Bedankt in ieder geval!

[whoami]

Bij mij geeft de query die ik gepost had, toch echt wel de User Defineds terug
Jouw query geeft bij mij niets terug.

Als ik echter dit doe:

code:

SELECT  * 
FROM sysobjects
WHERE status & 0xC0000000 = 0

Dan krijg ik primary key's en indexen terug (user defined), en ook 2 niet user defined:
pk_dtproperties, en DF__dtpropert_versi__.....

[Verwijderd]

Met de query

code:

SELECT   name, xtype
FROM   sysobjects
WHERE (status & 0xC0000000) = 0

Krijg ik de volgende resultaten terug:

TestProcedure	P
PK_Relation	PK
DF_EmailAddress_FK_EmailTypeID	D
PK_EmailAddress	PK
pk_dtproperties	PK
DF__dtpropert__versi__77BFCB91	D

Volgens google zijn die dt_ objecten iets voor versioning ofzo?

[edit]
Ik gebruik overigens Microsoft SQL server 2000 server EN de developers edition.

[ Voor 8% gewijzigd door Verwijderd op 17-06-2005 18:42 ]

[Verwijderd]

Wellicht kun je beter INFORMATION_SCHEMA gebruiken, als deze de informatie terug kan geven die je wilt hebben, i.p.v. sysobjects hacken.

Er is hier genoeg info over te vinden.

http://www.ispirer.com/do...Output/SQLWays-1-159.html
http://www.experts-exchange.com/Databases/Q_21248976.html
http://www.devnewsgroups....ork.adonet/topic3922.aspx


"Microsoft states that querying system tables isn't a good idea, but what are some possible consequences of doing so? We have a middle-tier application that runs a SELECT statement against the sysprocesses table. What effect could this query have on the database's performance?

Querying the sysprocesses table shouldn't cause artificial slowdowns although the table is heavily updated. (Also, sysprocesses isn't really a table in the traditional sense—it just looks like one in memory.) The problem is that Microsoft reserves the right to change the structure of system tables, even between service packs, so depending directly on a system table for certain data could cause your application to quit functioning after you apply a service pack or upgrade to a new release. Microsoft doesn't change the tables often, but when it needs to change a system table, backward compatibility isn't a major consideration. Therefore, Microsoft emphasizes that you shouldn't directly access the system tables. (For more information about system tables, see Kalen Delaney, Inside SQL Server, "System Tables," February 2000, and "Accessing System Tables," March 2000.)"
http://www.windowsitpro.c.../ArticleID/8776/8776.html

[ Voor 176% gewijzigd door Verwijderd op 21-06-2005 22:47 ]

[whoami]

Vreemd, ik gebruik ook Sql Server 2000.
Ik krijg met deze query:

code:

...
WHERE ( status & 0xC0000000) > 0

Stored Procedures en Tables terug (user defined).
en met

code:

WHERE ( status & 0xC000000 ) = 0

Informatie over indexen en pk's.

[Verwijderd]

Na nog eens goed nagedacht te hebben moet de juiste WHERE-statement als volgt zijn volgens mij:

code:

...
WHERE status & 0xC0000000 != 0xC0000000

bij een system object wordt volgens whoami een flag van 0xC0000000 ge-set. Dus als die specifieke flag gevonden wordt hebben we te maken met een system object.
Al die nullen zijn niet interessant, laten we kijken naar de C:

0xC = 12 (decimaal) = 1100 (binair). Als we zouden willen zien of 1100 geset is, moeten we idd AND-en met 1100. De scenario's die kunnen optreden zijn:

Waarde	& 1100
11xx	1100
10xx	1000
01xx	0100
00xx	0000

En wat heeft dit er nou mee te maken? Nou, in mijn eerste 'poging' vergeleek ik met 0. Theoretisch (en volgens mij in de praktijk ook) kunnen objecten ook eindigen met een patroon van 1000, wat volgens de tabel niet op 0 uitkomt. Echter, het is dan ook geen systeem object.

En waarom dan > niet goed is: Ik zie in de sysobjects tabel weleens negatieve waarden staan bij status. Dit houdt dus in dat we te maken hebben met een signed variabele, waardoor we een aantal records kunnen missen.

[raptorix]

Je kan een goed overzicht downloaden bij microsoft:

http://download.microsoft...IN98MeXP/EN-US/systbl.chm

[curry684]

whoami schreef op vrijdag 17 juni 2005 @ 18:25:
Als je dus deze query doet:

code:

SELECT * 
FROM sysobjects
WHERE xtype = 'P'
AND status & 0xC0000000 > 0

Dan zou je de user defined SP's moeten krijgen.

Da's stierenpoep, ik heb hier op SQL2k Dev Edition een database met tig user en system procedures, en die flag is redelijk @ random gezet. Ik heb exact 2 statussen in die tabel uberhaupt, 536870912 en 1610612736, en beiden staan op zowel user als system SP's.

[Verwijderd]

curry684 schreef op dinsdag 21 juni 2005 @ 14:54:
[...]

Da's stierenpoep, ik heb hier op SQL2k Dev Edition een database met tig user en system procedures, en die flag is redelijk @ random gezet. Ik heb exact 2 statussen in die tabel uberhaupt, 536870912 en 1610612736, en beiden staan op zowel user als system SP's.

Die query, enigszins aangepast, (dus status & 0xc0000000 != 0xC0000000) werkt wel, op een database met meer dan 1000 stored procedures totaal. Het resultaat van die query is toch echt wel alle user stored procedures.

[whoami]

curry684 schreef op dinsdag 21 juni 2005 @ 14:54:
[...]

Da's stierenpoep, ik heb hier op SQL2k Dev Edition een database met tig user en system procedures, en die flag is redelijk @ random gezet. Ik heb exact 2 statussen in die tabel uberhaupt, 536870912 en 1610612736, en beiden staan op zowel user als system SP's.

Waarom zou dat stierepoep zijn ?
Als je de source bekijkt van sp_MS_marksystemobject, dan zie je dat er een vlag gezet wordt in het status veld mbhv een OR operatie:

code:

 begin tran
    dbcc LockObjectSchema(@objname)
    update sysobjects set status = status | 0xC0000000
                        where id = object_id(@objname, 'local')
    commit tran

[curry684]

Dan is die database die ik checkte net zo verneukt als degene die 'm gebouwd heeft destijds, geloof ik rustig eigenlijk

[Dricus]

Met behulp van SQL-DMO objecten (zie MS SQL Books online) kun je gemakkelijk alle user stored procedures van een database enummen.

Je kunt deze objecten gemakkelijk benaderen met behulp van MS Visual Basic.

Het werkt ruwweg als volgt:

• Je kunt een SQL server benaderen mbv het SQLServer object.
• Via de Databases collection van het SQLServer object kun je de gewenste database opzoeken. Je hebt dan een Database object.
• Het Database object heeft een collection genaamd StoredProcedures. Via deze collection kun je voor elke stored procedure een StoredProcedure object benaderen.
• Via de SystemObject property van het StoredProcedure object kun je erachter komen of het een user of system stored procedure betreft.

[EfBe]

Verwijderd schreef op vrijdag 17 juni 2005 @ 18:31:
[...]
Dit klopt inderdaad, gelukkig!
één dingetje... Jouw voorbeeld geeft niet de user defined SP's terug, maar de system SP's... In theorie. Het veld status is van het type int, wat 0xC0000000 een negatief getal maakt. Dus om alle user defined stored procedures te vinden gebruik ik nu

code:

SELECT *
FROM sysobjects
WHERE sysobjects.xtype = 'P'
AND (status & 0xC0000000) = 0

Bedankt in ieder geval!

Dat status veld hoef je niet te checken. Je moet alleen niet op de master catalog deze query uitvoeren maar op een normale catalog. SqlServer kent nl. geen 'system' procedures.

Verder zit er een bug in INFORMATION_SCHEMA views die het ophalen van objects van schemas anders dan 'dbo' moeilijk maakt want de results kloppen niet altijd.

[Verwijderd]

MrHuge schreef op dinsdag 21 juni 2005 @ 23:41:
Met behulp van SQL-DMO objecten (zie MS SQL Books online) kun je gemakkelijk alle user stored procedures van een database enummen.

Je kunt deze objecten gemakkelijk benaderen met behulp van MS Visual Basic.

Het werkt ruwweg als volgt:

• Je kunt een SQL server benaderen mbv het SQLServer object.
• Via de Databases collection van het SQLServer object kun je de gewenste database opzoeken. Je hebt dan een Database object.
• Het Database object heeft een collection genaamd StoredProcedures. Via deze collection kun je voor elke stored procedure een StoredProcedure object benaderen.
• Via de SystemObject property van het StoredProcedure object kun je erachter komen of het een user of system stored procedure betreft.

Ik denk dat dit inderdaad de netste oplossing is, omdat je dan via een API die er voor bedoeld is de zoekopdracht laat doen. Ik denk dus dat ik voor deze optie ga. Thanks!

[whoami]

Ik denk dat die API gewoon ongeveer hetzelfde doet als die query, nl. checken of het status veld al of niet die vlag bezit.

[Dricus]

whoami schreef op woensdag 22 juni 2005 @ 09:27:
Ik denk dat die API gewoon ongeveer hetzelfde doet als die query, nl. checken of het status veld al of niet die vlag bezit.

Zou kunnen. Belangrijk verschil is echter, dat de API goed gedocumenteerd is, en de tabellen waarover gepraat wordt in deze thread volgens mij niet.

Volgens mij is werken via de SQL-DMO API 'the way to go' voor dit soort dingen. Je kunt op een zeer eenvoudige manier alle objecten van een SQL server benaderen.

[sig69]

Ik heb ooit eens een kleine app geschreven om in SP's te zoeken, daar gebruikte ik deze query:

code:

SELECT
    name 
FROM 
    sysobjects 
WHERE 
    xtype = 'P' AND 
    category = 0

[whoami]

sig69 schreef op woensdag 22 juni 2005 @ 13:14:
Ik heb ooit eens een kleine app geschreven om in SP's te zoeken, daar gebruikte ik deze query:

code:

SELECT
    name 
FROM 
    sysobjects 
WHERE 
    xtype = 'P' AND 
    category = 0

En, wat wil je daar mee zeggen ?

[Verwijderd]

Hij wil daarmee zeggen dat met die query hij ook alle user stored procedures schijnt op te halen (net even getest, lijkt te werken). Maar goed, via die API is het toch het eenvoudigste, en het produceert de netste code IMHO. Scheelt weer onderhoud.