Scenario:
You are working as SQL Server developer. The company you are working create a new database for each of the client with same objects such as tables/Stored Procedures. You have one stored procedure with the same name let's say dbo.LoadCustomer present in all the databases. You need to execute that Stored Procedure from multiple databases. The script can be used one time or you can also run by using SQL Server agent if has to run on schedule.
Solution:
We can use Cursor to loop through list of the databases in which our Stored Procedure is present and we want to execute. Below is sample script. Please change the @SPName and filter the list of database as per your requirements.
--Use Cursor to Loop through Databases DECLARE @DatabaseName AS varchar(500) --Provide the name of SP that you want to run DECLARE @SPName AS varchar(128) = 'dbo.loadCustomer' DECLARE DBCursor CURSOR FOR --Filter the list of the database in which Stored Procedure exists SELECT NAME FROM sys.databases WHERE database_id > 4 OPEN DBCursor FETCH NEXT FROM DBCursor INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DBName AS nvarchar(500); SET @DBName = QUOTENAME(N'' + @DatabaseName + ''); --USE Dynamic SQL To change the Database Name and --Execute Stored Procedure from that Database EXEC ( N'USE ' + @DBName + N'; EXEC(''' + @SPName + ' '');' ); FETCH NEXT FROM DBCursor INTO @DatabaseName END CLOSE DBCursor DEALLOCATE DBCursor
how can we create store procedure in multiple databases can you please guide me
ReplyDelete
ReplyDeleteGujarat Council of Educational Research and Training (GCERT) ready to Prepare new Syllabus for 1st, 2nd, 3rd, 4th, 5th Gujarati, English Medium All Subject Pdf Format, GCERT Every Year Elementary Gujarat 2nd Class Syllabus School Final Exam Conducted Month of April, This Primary School Exam Date Sheet 2023 Available at Official Website. GCERT is a State Government Body which is Responsible for Conducting Exams, Designing of GCERT Elementary School Syllabus 2023, Research, and Development in the State of Gujarat, Students who are Going to Appear in GCERT Primary Examination can Check the GCERT 1st, 2nd, 3rd, 4th, 5th Syllabus 2023.
Thank you for the detailed information. I attend a programmer course and we go through approximately the same training. I study at the university and there are tasks that I don’t understand how to complete. So I turned for help with writing nursing papers last year. Thanks to the writers, I passed my coursework and essays and finished the semester well.
ReplyDeleteLearning databases in IT is essential for understanding data management and retrieval. Superb papers in this field can significantly aid research, offering deep insights into database design, optimization, and administration. They provide comprehensive knowledge that can help students and professionals grasp complex concepts, stay updated with the latest advancements, and enhance their practical skills for real-world applications.
ReplyDelete