October 30, 2009 at 12:40 pm
Hi ,
I have Script File that generates output for all the objects in database.. Here is the script file ::
***not my own Script ***Copied from SQL SERVER Blog**
Use database_name
Go
select 'exec (''SET FMTONLY ON; ' +
case when routine_type = 'scalar_fn' THEN 'select ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' (' + LEFT(params, LEN(params)-1) + ')'
WHEN routine_type = 'table_fn' THEN 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' (' + LEFT(params, LEN(params)-1) + ')'
WHEN routine_type = 'procedure' THEN 'exec ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' ' + LEFT(params, LEN(params)-1)
WHEN routine_type = 'view' THEN 'select * from ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) END +
'; SET FMTONLY OFF; '')' AS statements
FROM (
SELECT routine_type, obj_schema, obj_name, RTRIM(REPLICATE('NULL, ', COUNT(*))) AS params
FROM
( select r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'scalar_fn' AS routine_type
from INFORMATION_SCHEMA.ROUTINES r
join INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
AND r.specific_name = p.specific_name
WHERE routine_type like '%FUNCTION%' and parameter_mode = 'IN'
and routine_body = 'SQL' and r.data_type <> 'TABLE'
UNION ALL
SELECT r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'procedure' AS routine_type
FROM INFORMATION_SCHEMA.ROUTINES r
JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
and r.specific_name = p.specific_name
WHERE routine_type = 'PROCEDURE' and routine_body = 'SQL'
UNION ALL
SELECT r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'table_fn' AS routine_type
FROM INFORMATION_SCHEMA.ROUTINES r
JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
AND r.specific_name = p.specific_name
WHERE routine_type = 'FUNCTION' AND parameter_mode = 'IN'
AND routine_body = 'SQL' AND r.data_type = 'TABLE'
UNION ALL
SELECT table_schema AS obj_schema, table_name AS obj_name, 'view' AS routine_type
FROM INFORMATION_SCHEMA.VIEWS
) t1
GROUP BY obj_schema, obj_name, routine_type
) t2
************************************
Which i got from one of the SQL SERVER blogs ..
Here .. the thing is .. The result set will in this form for all the Objects .. depends on the TYPE of the Object
****exec ('SET FMTONLY ON; select * from [dbo].[Object_name]; SET FMTONLY OFF; ')****
So , here I need to copy the resultset to new Window and execute .. Which is my final result set .
Can any one tell me please that
Can we updated the same Script file .. which Executes the Output of the Above Actual file ..and gives the final Result ..
Thanks a lot
John
October 30, 2009 at 1:16 pm
Are you sure you want to execute all the stored procedures in a database with all NULL parameters being passed? It looks like this will select * from every function with NULL parameters passed also. You can do this with a cursor but make sure the stored procedures don't do anything unexpected with all NULLs being passed to them. Currently this will only print the commands to execute. You can uncomment the line indicated to run each statement.
DECLARE @sql VARCHAR(MAX)
DECLARE commands CURSOR FOR
select 'exec (''SET FMTONLY ON; ' +
case when routine_type = 'scalar_fn' THEN 'select ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' (' + LEFT(params, LEN(params)-1) + ')'
WHEN routine_type = 'table_fn' THEN 'SELECT * FROM ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' (' + LEFT(params, LEN(params)-1) + ')'
WHEN routine_type = 'procedure' THEN 'exec ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) + ' ' + LEFT(params, LEN(params)-1)
WHEN routine_type = 'view' THEN 'select * from ' + QUOTENAME(obj_schema) + '.' + QUOTENAME(obj_name) END +
'; SET FMTONLY OFF; '');'
FROM (
SELECT routine_type, obj_schema, obj_name, RTRIM(REPLICATE('NULL, ', COUNT(*))) AS params
FROM
( select r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'scalar_fn' AS routine_type
from INFORMATION_SCHEMA.ROUTINES r
join INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
AND r.specific_name = p.specific_name
WHERE routine_type like '%FUNCTION%' and parameter_mode = 'IN'
and routine_body = 'SQL' and r.data_type <> 'TABLE'
UNION ALL
SELECT r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'procedure' AS routine_type
FROM INFORMATION_SCHEMA.ROUTINES r
JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
and r.specific_name = p.specific_name
WHERE routine_type = 'PROCEDURE' and routine_body = 'SQL'
UNION ALL
SELECT r.specific_schema AS obj_schema, r.specific_name AS obj_name, 'table_fn' AS routine_type
FROM INFORMATION_SCHEMA.ROUTINES r
JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.specific_schema = p.specific_schema
AND r.specific_name = p.specific_name
WHERE routine_type = 'FUNCTION' AND parameter_mode = 'IN'
AND routine_body = 'SQL' AND r.data_type = 'TABLE'
UNION ALL
SELECT table_schema AS obj_schema, table_name AS obj_name, 'view' AS routine_type
FROM INFORMATION_SCHEMA.VIEWS
) t1
GROUP BY obj_schema, obj_name, routine_type
) t2
OPEN commands
FETCH NEXT FROM commands into @sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
--EXEC (@sql) --uncomment this line to execute each command
FETCH NEXT FROM commands INTO @sql
END
CLOSE commands
DEALLOCATE commands
October 30, 2009 at 1:26 pm
Thanks Matt ..
Yeah ! i want to execute all the objects with NULL .. the thing i want to do this is "TO check Runtime Errors "
your Script also gives me the same Output .Actually i am trying to get the output for the Result Set of the Script
exec ('SET FMTONLY ON; exec [dbo].[storeprocedure1] NULL, NULL, NULL, NULL; SET FMTONLY OFF; ');
exec ('SET FMTONLY ON; exec [dbo].[storeprocedure2] NULL; SET FMTONLY OFF; ');
exec ('SET FMTONLY ON; exec [dbo].[storeprocedure3] NULL; SET FMTONLY OFF; ');
exec ('SET FMTONLY ON; exec [dbo].[storeprocedure4] NULL, NULL; SET FMTONLY OFF; ');
exec ('SET FMTONLY ON; exec [dbo].[storeprocedure4] NULL; SET FMTONLY OFF; ');
exec ('SET FMTONLY ON; select [dbo].[FUNCTION1] (NULL); SET FMTONLY OFF; ');
October 30, 2009 at 1:34 pm
At the bottom of the script is a line that starts with --EXEC (@sql)
Remove the -- and it will run each statement.
Good luck
October 30, 2009 at 1:41 pm
Thanks a Lot Matt,
I miss that Uncomment Part .. thanks It working ..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply