Menu
i am trying to grant execute privs in stored proc in more than one database. The problem is this stored proc might not be in some of the databases. So how can i write a script which checks if stored proc exists in database and if does then provide execute privs for user?
915
Using SQL Server Management StudioExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure and then click View Dependencies. View the list of objects that depend on the procedure.
In the Object Explorer in SQL Server Management Studio, go to the database and expand it. Expand the Programmability folder. Right Click the Stored Procedures folder. From the right-click menu, select Filter in the right-click menu.
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system (RDBMS) as a group, so it can be reused and shared by multiple programs.
Use the COL_LENGTH system function! You basically just pass the name of the table your interested in, and the name of the column within that table you want to check. This function returns the defined length of the column in bytes, if that column exists. If the column does not exist, the function returns NULL.
many ways to do it:
1)
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'proc1'
AND type = 'P')
2)
IF EXISTS (SELECT *
FROM information_schema.routines
WHERE routine_name = 'Proc1')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
