I need to get the definition of some objects in the SQL server. I got the definitions of stored procedures, functions, views and triggers from sys.sql_modules. But, I don't know how to get that of user-defined table types and of sequences. I am trying to create this in another database and automate this process. Does anyone know how to get the definition of table types and sequences?

Running a series of automated checks on our databases for the security folks checking our STIG compliance. Mostly simple stuff like certain parameters turned off, etc. We're in the stone ages, so this is being done with Sqlplus scripting to populate the reports. Using sqlplus -S to filter out that unnecessary login message, but the security folks would also like the command with the finding details instead of just the output. With sqlplus -S, the output is

VALUE
------
FALSE

whereas we want

Select blah blah;

VALUE
------
FALSE

Set echo on doesn't fix this, even when nesting the query in a sql script and run with @. I'm assuming the S option overrides the echo setting. Wondering if there's an option I'm not familiar with or if I just need to work around it.

We have several oracle 19c servers with daily exports. I would like to find a way to run a command to read the export log file to find the success message for the day. e.g. I would like to putty into the linux server have a command I can copy and paste that will read the daily export log file and tell me in one command/output that the export was successful or not? or any better ideas to automate reporting that daily exports are successful on 40+ servers?

I am asking about *pgAdmin* internal user management , not *Postgres* users-and-roles. For teaching a beginning database class, I am trying to procedurally configure *pgAdmin* in Server Mode, using shell scripts, *Podman*, etc. In addition to the Administrator and User roles, I want to create a Student role with restricted permissions. The setup.py feature offers a command-line interface for configuring pgAdmin. It offers commands like: - add_user - get_role 👉🏽 Is there some procedural way to define a Role with Permissions? I need a console command equivalent for the *Permissions* tab in the *pgAdmin* GUI: I want to procedurally create a Role that effectively has some of those checkboxes unchecked.

I have a database that contains a version table with one value stored in it which is the database version number. I would like to be able to update the database version automatically using numbered scripts. If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below: 001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence? Should I use an SQL script or create a bash script? I am a little stuck on how to approach this so any pointers or advice will be much appreciated...

Trying to export SQL Objects via DBAtools in Powershell. Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql The same script working fine and exporting the job details. But, not working on a few servers, and returns the below error/warning > WARNING: [06:05:42][Get-DbaAgentJob] Failure | The value's length for key 'Data Source' exceeds its limit of '128'.

I have some large DB that I need to restore to another db from time to time, this process take some time to conclude. The annoying thing I found out in last version of SSMS is that I cannot script any sp of the current db while I do this restore. In my case I have the current db named FleetXS that is online and is in use and I do the restore of backup of this db with the date of 2 days ago in other db named Fleetxs_Restore. For all the time of the restore it's not possible to script any sp of db FleetXS with the weird error > Cannot open database "FLEETXS_restore" requested by the login. The > login failed. Login failed for user 'anna'. (Microsoft SQL Server, > Error: 4060) I have no need to open the database FLEETXS_restore that even is not actual to date, I operate within my current database FleetXS that is online, why on the earth my login "requested db FLEETXS_restore"? Is there any workaround or should I wait for hours when the restore finishes to be able to get the text of sp of **another** database? > Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) > Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation > Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 > (Build 14393: ) (Hypervisor) - SQL Server Management Studio 15.0.18390.0 - SQL Server Management Objects (SMO) 16.100.46521.71 - Microsoft Analysis Services Client Tools 15.0.19714.0 - Microsoft Data Access Components(MDAC) 10.0.19041.1 - Microsoft MSXML 3.0 6.0 - Microsoft .NET Framework 4.0.30319.42000 - Operating System 10.0.19043 I do not use SSMS to do the restore, I use my own script. There are two backups to restore, full + diff. I do full with norecovery and diff with recovery.

When I go to Object Explorer and expand down to Stored Procedures (or Tables), right-click on one of them and choose "Script Stored Procedure / Table as" > "CREATE To" > "New Query Editor Window", it takes a while (becomes unresponsive) and then brings another object (function / table). If I do the same but choose other action (for instance, "ALTER" for Procedures or "DROP And CREATE") instead of CREATE, it brings the correct object. It started to behave that way without any clear reason, except for, perhaps, the last SSMS update. I thought I had successfully used CREATE just a few minutes before noticing that error, but later I realized I've used ALTER and not CREATE. I've tried different databases and even different servers, with the same results. I've already restarted the machine but it didn't change anything. I've also asked a coworker to do the same steps and he didn't get any errors in his machine. Obs.: It has nothing to do with permissions; it is also happening with my local SQL Express installation (where I have full access). Is there any template for those script generation? If yes, where are they stored? If not, how can I possibly fix that? Version information:

Microsoft SQL Server Management Studio = 13.0.16100.1;
Microsoft Analysis Services Client Tools = 13.0.1700.441;
Microsoft Data Access Components (MDAC) = 10.0.14393.0;
Microsoft MSXML = 3.0 6.0;
Microsoft Internet Explorer = 9.11.14393.0;
Microsoft .NET Framework = 4.0.30319.42000;
Operating System = 6.3.14393;

I want to delete my export files daily from ASM diskgroup with a crontab job. I want to prepare a script for it. ASMCMD> ls exp1.dmp exp2.dmp exp3.dmp exp4.dmp ASMCMD> pwd +DATA/EXP How can I prepare this script? I prepared a template for it, but I couldn't script it.

set_gridenv
asmcmd
cd +DATA/EXP
rm -rf exp*.dmp
exit

There is a modification project in our Process Plant related to SCADA application (Process Automation System) and I am looking for reliable advice/ suggestions from experienced members related to SQL Database Management. **Background:** We have multiple Workstations each running Stand-alone SCADA application with SQL Express for writing Real-time Alarms to SQL DB and occasional reading. We need to forward these Alarms SQL DB (Table only) from all Workstations to Centralized Station, merge all Alarm Tables into one, and then import to SQL Express DB on Centralized Station for Read-Only Purpose. These tasks need to be automated and triggered at appropriate frequency (for eg. every 1 Hour). Please note that all Workstations are Workgroups and Stand-alone. Please also note that communication between Workstations and Centralized Station is strictly uni-directional i.e. Workstation -> Centralized Workstation (so no option for SQL Server Replication). *** ### My Rough Plan #### On Each Workstation: 1. Duplicate the Production_DB to Offline_DB (in same SQL Server Instance) 2. Synchronous/Asynchronous update of Alarms Table from Production_DB to Offline_DB. 3. Periodic Conversion of the Alarms Table from Offline_DB to CSV. 4. Forwarding the CSV to Centralized Workstation. #### On Centralized Workstation: 5. Creating Workstation_DB for each Workstation in SQL Server instance. 6. Converting and importing CSV file in each Workstation_DB to Alarms Table. 7. Creating one Central_DB and merging all Workstations Alarms Table to one Table. 8. Assign Central_DB to SCADA Application on Central Workstation. Please advise if the rough plan needs correction or optimization. I have no background knowledge of SQL scripting and I would be grateful if someone also guide me through the execution of each step in SQL (excluding step 4 and step 8).

Working on a script to drop databases . In some of the environments our databases are regularly restored from live, replication (merge or transactional) established, processes run and tested, then, the databases need to be dropped, as all of the development has been deployed to live, now another cycle starts. as part of automating the dropping of the database so far I came out with this script below:

select
DROP_DB_SCRIPT='use master;' + char(13) 
+ case when source_database_id is not null  -- this is a database snapshot
     then '' 
      else 
	    ' alter database ' + name + ' set single_user with rollback immediate ' +              -- put db in single user mode 
	    case when (sb.is_published = 1 or sb.is_merge_published = 1) 
		     then 'EXEC sp_removedbreplication ' + '''' + name + ''''                           -- remove db from the replication 
		     else '' 
		end +char(13) 
  end  +char(13) +
' drop database ' + quotename(name) +char(13) 

,*
from sys.databases sb
where database_id >= 5
and is_distributor= 0

The script above will not do anything other than produce a script that I can use to drop a desired database or set of databases. it takes into consideration replication, (you need to remove the replication separately), and if the database is a database snapshot , which is a different thing . I have not considered the following: Should an OFFLINE database be SET EMERGENCY before dropping? the question here is: Is there anything, any check, that you can see I should have added but have not? I have dealt with 2 things: 1. database is a snapshot 2. database is involved in replication I have not dealt with: 1. Database is part of an availability group (in that case I would need to remove it from the AG first) 2. anything else that you can see?

Recently, I've been trying to make our company's full backup process more efficient. In order to do this, my team has decided that instructing the backup job to tackle the full backups starting with the smallest databases and ending with the largest is the best way to meet our objectives. To do this, I added a cursor to the existing job to set the database order before running: DECLARE @db_name VARCHAR(100), @db_id INT, @db_size INT; PRINT '**********Order Databases by Size**********' PRINT ' '; DECLARE DB_by_Size CURSOR FAST_FORWARD FOR SELECT db.[name], db.database_id, mf.size FROM sys.databases AS db INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id WHERE mf.[type] 0 AND db.name 'tempdb' ORDER BY mf.size; OPEN DB_by_Size FETCH NEXT FROM DB_by_Size INTO @db_name, @db_id, @db_size WHILE @@FETCH_STATUS = 0 BEGIN EXEC BackupScripts.dbo.DatabaseBackup @Databases = @db_name, @Directory = N'\\*****\Backups', @BackupType = N'Full', @Compress = N'Y', @LogToTable = N'Y', @Period = N'Weekly', @Execute = N'Y' FETCH NEXT FROM DB_by_Size INTO @db_name, @db_id, @db_size END; CLOSE DB_by_Size; DEALLOCATE DB_by_Size; GO I've been running the job like this for a couple weeks and observing the run times for each database in the command logs: USE BackupScripts SELECT * FROM dbo.commandlog WHERE StartTime >= 'yy-mm-dd 00:00:01.000' AND StartTime 'UPDATE_STATISTICS' AND CommandType = 'BACKUP_DATABASE' AND Command NOT LIKE '%\Nightly\%' ORDER BY StartTime DESC When I look at the results for the above query, the longest-running backups aren't happening at the end like I expected. The run times seem to be pretty arbitrary instead of more or less from shortest to longest like I was trying to do. I think this has something to do with the way I'm measuring size (correct me if I'm mistaken here), so I'm thinking mf.size doesn't seem to be a good measure to accomplish what I'm going for. Does anyone know a better table to pull my database size information from instead of sys.master_files? Or a better way to gauge how long a database will take to run a full backup before it actually runs the job? I've been hunting around for awhile, and can't get an accurate read from the tables I'm finding. Thanks!

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample script to do this? Thanks!

I'm starting with powershell and this time. I have to read all the values from a excel file In this case from a specific sheet. The ultimate goal of this task is to get the values from two columns and then save this values and export to a SQL Table because I have a StoreProcedure to read the table and then create all extended properties needed in one database with thouse values This is the code but I not able to get correctly the data from excel $Excel = New-Object -ComObject Excel.Application $Path = '' #I'm using a shared folder $Workbook = $Excel.Workbooks.Open($Path) $workSheet = $Workbook.Sheets.Item(4) #The number is for because the excel file has 4 sheets #and the sheet desired is the last one $Output = @() $Output = [pscustomobject][ordered]@{ #I think with this I could go through the table but I #would have to calculate the coordinates first. Rol = $Worksheet.Range("A4").text Member = $Worksheet.Range("B4").text } Example of how looks the sheet | Rol | Member | Description | |:---- |:------:| -----:| | Admin | name + email | Description of the rol | | Data Lead | name + email |Description of the rol | | Arquitect | name + email | Description of the rol | | DBA | name + email | Description of the rol | | QA | name + email | Description of the rol | So, my intention is go through the table and get the date as array but to take into account. Sometimes a role can have several members associated with it and that's means more rows in member column Best Regards and thanks in advance

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,cid ascending and update column xid with incremented values starting with 0. What's the best way to achieve that? My current solution involves: - selecting table T - openining a cursor over the ordered tuples of a table - assigning the autoincrement value to xid - inserting the tuple into a temporal table T_temp - deleting all records from T - inserting all records from T_temp into T Since the tables have different schemas, I programmed half of the code in PL/pgSQL and half of the code with bash scripting. **Question 1:** Any comments how to have it program that in pure PL/pgSQL? **Question 2:** Any comments how to implement that more elegantly?