This script is intended for SQL Server professionals that needs to have a quick snapshot from an environment with all relevant information about the databases. It's a compilation from multiple scripts, and works in SQL Server 2000.I think that works on 7.0 too, but may have some compilation issues.Don´t forget to see the section MANUAL […]
2003-04-28
4,795 reads
to figure out space used by tables. I have seen different versions of them .. here is a very simple effective one!!!
2003-04-28
105 reads
This is one example of how you can make backups with a date stamp for the file name. You can then periodically remove the files that are older than a specified date by simply placing a few lines of code in a job step and calling the supplied sp and passing the date from which […]
2003-04-25
787 reads
This stored procedure will list all user tables in the current database, along with the number of rows in the table. This version uses dynamic SQL to retrieve the number of rows, to overcome the need for an index on all tables. I use this to document legacy databases, I find that they may have […]
2003-04-24
752 reads
This script was designed to restore a backup of a production database from disk file, handle multiple DB files, move the files to new location, with new database name that is customized for the date of the backup, create any missing logins, and fix any user accounts that are orphaned by differing SIDs from the […]
2003-04-24
1,085 reads
I see scripts all the time on how to reindex all the tables in a db but they are usually too generic and may cause issues. For instance they typically cause the log file to grow very large and may even fill the hard drive. This is intended to be run off hours when db […]
2003-04-24
683 reads
This query calculates the running Total on Table without using cursors or variables.
2003-04-22
405 reads
For security and auditing purpose, in some cases there would be a need of tracking transaction detail on a table.The following script achive this using Triggers. The auditedevents will be stored in a separate table.
2003-04-19
276 reads
This procedure is just another approach for getting a list of tables defined in a database, ordered by dependency on other tables based on foreign key relationship.I used it for a deployment process, where the TABLE create scripts must be executed in the correct order,because the foreign key constraints are specified in the create script […]
2003-04-18
402 reads
It is a painful experience for all us administrators to truncate logs regularly. I just created a job to executesp_msforeachdb "use ?; backup log ? with truncate_only ; dbcc shrinkfile(2,5)"on all the servers at frequent intervals. It has saved me a lot of time. hope it helps you too.n.b: I assume that file id 2 […]
2003-04-18
336 reads
By Steve Jones
I use ConEmu for my terminal interface. I’m still on Windows 10 at home,...
Creating a Fabric workspace takes about 30 seconds. Restructuring workspaces after people have built...
By Steve Jones
It’s a small change, but a handy one. Flyway Desktop (FWD) now includes the...
Comments posted to this topic are about the item Adding new column with DEFAULT...
Comments posted to this topic are about the item Advanced Deployment Scenarios: Stairway to...
Comments posted to this topic are about the item You Need a DBA Pipeline
Which number did the two COUNT(*) return:
DROP TABLE IF EXISTS #tmp CREATE TABLE #tmp (id INT NOT NULL) INSERT INTO #tmp (id) SELECT gs.value FROM GENERATE_SERIES(1, 5) AS gs ALTER TABLE #tmp ADD my_value INT NOT NULL CONSTRAINT df_tmp_my_value DEFAULT 1 SELECT COUNT(*) FROM #tmp AS t WHERE my_value = 1 ALTER TABLE #tmp DROP CONSTRAINT df_tmp_my_value ALTER TABLE #tmp ADD CONSTRAINT df_tmp_my_value DEFAULT 2 FOR my_value SELECT COUNT(*) FROM #tmp AS t WHERE my_value = 1See possible answers