Questions tagged [scripting]

For questions about generating or using language scripts, for example scripting a database object definition.

Filter by
Sorted by
Tagged with
7
votes
3answers
8k views

Restore All Databases Script

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 ...
18
votes
5answers
26k views

SQL Server 2012: Generate Scripts from command line

I am running SQL Server 2012. The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts. Is there a way to automate that via command line ...
1
vote
1answer
3k views

Import data from one Database to another script

As part of a data migration I would like to script a data transfer from the existing to a new database. I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012. Note the ...
6
votes
1answer
2k views

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

Due to business logic, we need a new column in a table that is critical to ensure is always populated. Updating existing references that insert/update is difficult but doable, but ensuring future code/...
2
votes
1answer
138 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is the ...
17
votes
6answers
4k views

Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project

I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema ...
2
votes
1answer
7k views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','...
2
votes
1answer
688 views

How to script out the grant view on login permission?

I create the following login without any permissions. USE [master] GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe') CREATE LOGIN [Radhe] WITH PASSWORD=N'...
2
votes
2answers
2k views

What is running right now?

I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there. Something is running on my server at 3am every morning that is ...
1
vote
1answer
156 views

How do I get Postgres DO $$ to work from the bash prompt?

Postgres 9.6.9 on Linux RHEL 6.10 If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail. So I switched ...
9
votes
2answers
4k views

Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?

I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks. So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities. ...
14
votes
3answers
25k views

Script out Oracle DDL in an automated fashion

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention. I know of DBMS_METADATA.get_ddl(), but have found that the ...
18
votes
3answers
123k views

Easy way to check connectivity to SQL Server from client

For troubleshooting purposes, I would like to be able to check if a client can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server. Is there ...
2
votes
3answers
5k views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
1
vote
1answer
2k views

Generate script for SQL Broker objects

I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database: but the objects related to SQL Broker (messages, queues and services) are not generated. I've ...
0
votes
2answers
220 views

How to script all the service broker elements for each database it is on, in the current server?

I have service broker running on different servers. Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there. For the next time I need to ...
8
votes
1answer
2k views

Why does SQL Server Mangement Studio not script Indexes by default?

Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time. Could anyone please explain the reason? Is there any insight about this ...
3
votes
3answers
993 views

Save Results of DBCC ShrinkFile

When we execute the following command: DBCC SHRINKFILE('MyDB_log', 1) We get the following results in SSMS: DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages -----|-------|-----...
2
votes
2answers
2k views

How can can I get object definitions stripped of all comments?

When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments. Is this possible by a SQL ...
2
votes
1answer
2k views

ERROR: “sql ” is not a known variable

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running. I'm invoking the cobertura.sql file using ...
2
votes
0answers
414 views

SSIS Script Component errors sporadically with the same code

I have a SSIS Data Flow Task that takes CSV output (from a flat file connection) and passes that to a Script Component. When I run the task I get the following error: Script Component has ...
2
votes
0answers
925 views

How do I create an automated SQL Server restore script for multiple backups? [duplicate]

I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script ...
2
votes
2answers
2k views

parse rs.status() output to display one secondary node for mongodump destination

I wish to run mongodump to secondary (not primary). So I need as output hostname and port of one secondary. # ./mongo -udbadm admin --port 27100 -p --quiet --eval "printjson(rs.status().members.map(...
1
vote
1answer
1k views

MySQL backing up MyISAM tables by copying the physical files - and automating the process

I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly ...
1
vote
2answers
2k views

script all triggers in a database

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications. Every time I need to reinitialise those database I need to script ...
1
vote
1answer
454 views

Numbering rows consecutively for a number of tables

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,...
1
vote
1answer
2k views

ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works)

I made a script which executes successfully online backup when it is run manually by the instance owner. Script is the following (two lines originally). #!/bin/bash /opt/ibm/db2/V9.7/bin/db2 ...
0
votes
1answer
1k views

List of role permissions including other roles - how can I find out the roles that belong to other roles?

I can see the permissions of a role in a database using the scripts in List all permissions for a given role? However, It does not show me when the role belongs to a another role. For ...
0
votes
2answers
4k views

Set default value for all columns with specific name within a database

I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that ...