July 6, 2025 at 4:20 am
Hi everyone
I have two db - one is test and other is archive. In the test db I have SP that begin with "WIP" and "z". WIP means work in progress and z means retired. I want to move all SP that begin with "WIP" or "z" from test db to archive db. Is there a script that can do this for me? I have over 50 such SP so I prefer to not do it manually.
Thank you
July 6, 2025 at 4:51 am
Actually...I ended up moving them manually. No need for code.
July 7, 2025 at 3:06 pm
I know you already did this manually, but I'd recommend using something like Visual Studio/ADS (I think this is being retired)/VS Code and GitLab/GitHub. I recommend some self-hosted git so you can keep your code more secure, but that's just me. Even a gerrit or bitbucket solution would be better than crossed fingers that you never need to roll back changes. It is a bit more work to maintain it in a code repository, but when you need to rollback code changes OR you do a code review that catches a bug, you will appreciate having it in source control.
Get your SP's into source control. Then you can deploy straight from visual studio to your test/prod systems.
This also lets you do easier code review, have revision control on your SP's for faster rollback if something breaks, and reduces risk.
Mind you, I need to take my own advice but it is on my radar. None of my stuff is in code review; partly my fault for not encouraging it, and party my teams fault for not helping to keep the code in source control. It's a bit of extra overhead for the developers, but in my opinion it is worth it. It has been on my radar for a few years and just have had trouble finding time and manager buy-in, but got a new job role with a new manager who I think will be more on board with me doing it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 8, 2025 at 1:18 pm
I know you fixed this already. Well done. However, figured I'd post this in case you, or anyone else, needs to hear it.
Databases are code.
They are. Yes, yes, the data isn't. I get it. What's stored in them isn't code. However, every single thing you do to your database, from making tables, to modifying functions, is code. What do we do with code? We put it in source control. Why? Well, easy management, team access, audit trails, history, all that stuff. Plus, automation. Want to move all procedures from one database into another? Silly simple when you're already in source control with your database code.
So, start there. Get your database code into source control and then start, all code, in source control first. It solves tons and tons of problems all by itself. THEN, it lends itself to making automation easier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2025 at 3:40 am
I like the idea of source control. Makes sense. I prefer an on-prem solution vs github etc. Can I do this in SS? Or do I need an external program? I am using VS for SSIS so is that a possibility?
July 9, 2025 at 3:44 am
My current attempt at source control is quite basic. I use Task -> Generate Scripts and save the script file. This way I have a snapshot of all tables, SP, indexes etc. It is not the most efficient way to do it because there are quite a few limitations. It serves my primary goal - I have a backup of the tables and SPs that can be used to reference previous versions of code in case I have a need for it.
July 11, 2025 at 8:49 pm
I would get some git system. Downloading and storing files on disk is not a good long term solution for source control. GitLab (for example) has a free tier that you can install on-premise and use. I like GitLab and it is what we use at work (paid as we needed some features not available in free), but there are other solutions like gerrit too. Plus GitLab is on-prem; I believe Gerrit and bitbucket are too. Gitlab also has a cloud offering but it is not required to have your stuff in the cloud.
Offhand, I am not sure what edition of VS is needed for git support, but I think any of them support it. And if they don't it's not hard to push or pull stuff from the command line or with freely available tools (sourcetree for example).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 12, 2025 at 2:31 am
I am rookie but I am very interested in learning what the pros do so my coding experience and code quality improves. I have been doing some reading on gitlab. I have some questions. VS has option to connect to SS (Tools -> Sql Server). Currently I use SSMS for table/query development/management. If I use Gitlab then can I still use SSMS or will I have to use VS?
July 14, 2025 at 5:34 am
SSMS 21 allows you to integrate directly in to GIT.
July 16, 2025 at 11:01 am
try this
DECLARE @ProcName NVARCHAR(255),
@SchemaName NVARCHAR(255),
@FullProcName NVARCHAR(512),
@Definition NVARCHAR(MAX),
@sql NVARCHAR(MAX);
-- Cursor to iterate through procedures starting with WIP or z
DECLARE proc_cursor CURSOR FOR
SELECT
s.name AS SchemaName,
p.name AS ProcName
FROM
sys.procedures p
JOIN
sys.schemas s ON p.schema_id = s.schema_id
WHERE
p.name LIKE 'WIP%' OR p.name LIKE 'z%';
OPEN proc_cursor;
FETCH NEXT FROM proc_cursor INTO @SchemaName, @ProcName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullProcName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ProcName);
-- Get definition
SELECT @Definition = OBJECT_DEFINITION(OBJECT_ID(@FullProcName));
-- Create the procedure in archive DB
SET @sql = '
USE archive;
IF OBJECT_ID(''' + @FullProcName + ''') IS NOT NULL
DROP PROCEDURE ' + @FullProcName + ';
EXEC(''' + REPLACE(@Definition, '''', '''''') + ''');';
EXEC (@SQL);
-- Optionally drop from test DB
SET @sql = 'DROP PROCEDURE ' + @FullProcName;
EXEC (@SQL);
PRINT 'Moved procedure: ' + @FullProcName;
FETCH NEXT FROM proc_cursor INTO @SchemaName, @ProcName;
END
CLOSE proc_cursor;
DEALLOCATE proc_cursor;
July 19, 2025 at 10:21 am
You can use a script to generate and move all stored procedures starting with "WIP" or "z"—a dynamic SQL script using INFORMATION_SCHEMA.ROUTINES should help automate this across your test and archive databases.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply