Tag Info

Hot answers tagged

15

Use the -v switch to pass in variables. sqlcmd -v varMDF="C:\dev\SAMPLE.mdf" varLDF="C:\dev\SAMPLE_log.ldf" Then in your script file CREATE DATABASE [SAMPLE] ON PRIMARY ( NAME = N'SAMPLE', FILENAME = N'$(varMDF)' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SAMPLE_log', FILENAME = N'$(varLDF)' , SIZE = 29504KB , ...


10

You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers: t1.name <> t2.name is always true if t1.name = REPLACE(t2.name, 'DUP_', '') - you can drop the former usually you want union all. union means union all then drop duplicates. It might make no difference in this case but always using union ...


6

I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell. Then you also have SQL Server PowerShell Extenstions project over on ...


6

Here is an alternative to the test_attribs_unpivot view provided by JackPDouglas (+1) that works in versions before 11g and does fewer full table scans: CREATE OR REPLACE VIEW test_attribs_unpivot AS SELECT ID, Name, MyRow Attr#, CAST( DECODE(MyRow,1,attr1,2,attr2,3,attr3,4,attr4,attr5) AS VARCHAR2(2000)) attr FROM TEST_ATTRIBS CROSS JOIN ...


5

If the server is using TCP/IP, then the simple way is to just telnet to the SQL Server port and see if it connects. By default, that's port 1433, so this should work: telnet servername 1433 That will probably be appropriate in most cases. If it's using a different port, or dynamic ports (common with a named instance), then you'll need to determine which ...


5

In a shell script: #!/bin/bash sqlplus user/pass@server/DATABASE<<THEEND -- Change "1" to the desired fatal return code whenever sqlerror exit 1; @yoursqlscript.sql quit; THEEND Or you can just run: sqlplus user/pass@server/DATABASE @yoursqlscript ... and put the whenever sqlerror exit 1; at the top of your .sql script(s).


5

As well as using SSMS, you can use SSMS Tools Pack I prefer it: more options, batching, cleverer all round. Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and ...


5

If I understand correctly, you want to restart the numbering with 0 for every table. Use the window function row_number() in an UPDATE: UPDATE tbl t SET xid = n.xid FROM (SELECT ctid, row_number() OVER(ORDER BY aid,bid,cid) - 1 AS xid FROM tbl) n WHERE t.ctid = n.ctid; Using ctid as poor man's surrogate for a primary key, since you neglected to ...


4

I started to play with RedGate's tool - Script Manager. You can have them together in a single place with some UI. It may worth a try :).


4

How do I make the DROP wait for the CREATE CLUSTERED to finish? You don't, it already does. The statements are sequential, i.e. synchronous, one after the other. What is heap storage? Paul Randal explains So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom ...


4

The end of your post is really the important bit: information regarding what best suits the needs of database administrators. So let's start there. What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better ...


4

ksh or bash let you script items through SQL*Plus, and you can do quite complex stuff through this. However, shell scripting tends to be a bit 'write-only' and sh and its derivatives aren't really much good for developing complex program logic. For running automated tasks they're OK. For complex client-side data manipulation, not so good (sed/awk ...


3

You can't select columns from the view that aren't exposed in the view definition. If your view only includes three columns in the SELECT list, you can only reference those three columns in queries against the view. You can't use table aliases that are defined in the view in queries against the view. You cannot, therefore, reference the e2 alias when you ...


3

I had to do this for NPI (non-public information) reasons at a previous employer. Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else. Here's a rough version of the script. Season to taste. Also, this assumes you have first and last names in separate fields. CREATE TABLE #RandomData ( ...


3

If you don't want to know what the name is but for whatever reason you want to keep something in the table for that record, you can always just use a hash of the name: UPDATE MyTable SET Name = HASHBYTES('SHA1', Name) This will keep a unique value for each name record, but you will have no way to know what the name is. You can, however, see if a name ...


3

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


3

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem. e.g. DECLARE data CLOB; objType varchar2(30) := 'TABLE'; objSchema varchar2(30) := 'SCOTT'; objName varchar2(30) := 'EMP'; fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql'; ...


3

I often encounter the similar problem to compare two versions of a table for new, deleted or changed rows. Some month ago I published a solution for SQL Server using PowerShell here . To adapt it to your problem, I first create two views to separate the original from the duplicate rows CREATE OR REPLACE VIEW V1_TEST_ATTRIBS AS select * from TEST_ATTRIBS ...


3

In SQL*Plus, the Oracle command line too, you can use the syntax: SQL> @http://blah.com/myscript.sql And run all your scripts off your own personal webserver if you like. My good friend Vishal does this. You just need to get a VM or something of your own, these are cheap now, a quick Google shows £10/month. Just be certain it doesn't stipulate ...


3

DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual: DROP DATABASE cannot be executed inside a transaction block. Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...


3

You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch. I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter. IF OBJECT_ID('dbo.function_name') IS NULL BEGIN DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name() RETURNS INT AS ...


3

As you describe it, it's not going to work. The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session. One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.


3

Using a batch file and SQLCMD, you can execute the script as below : cls echo off set DbServer=Server_name\InstanceName set MyLogin=sa set MyPassword=StrongPassword set MasterDbName=master set DbName=AdventureWorks set SQLCMDPath=C:\Program Files\Microsoft SQL Server\90\Tools\binn set SpScripFilePath=C:\ScriptFolder set SpScripFileName=test.sql echo ...


3

From the documentation: createdb is a wrapper around the SQL command CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server. This means that you can write a script which issues a command like psql -f createdb.sql -v passwd=$passwd -v user=$user Where ...


3

No version of SQL Server has this. It would be useful (PostreSQL has the same and I like it). In SQL Server, there are table types (CREATE TYPE), temporary tables (CREATE TABLE #foo) and table variables (DECLARE @var TABLE), but you can't mimic or inherit a table definition using %TYPE or CREATE TABLE .. INHERITS/OF


3

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use EXEC sp_change_users_login 'Auto_Fix', 'username' for fixing user accounts. The Wizard approach sounds doable. I you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier version of SQL Server still works with ...


2

If you migrate to a new version of PostgreSQL use pg_dump from the latest version. Anyway, this is a simple way to dump and restore : pg_dump -U username -h hostname source_database | psql -U username target_database


2

If your destination database has only one additional column, I think the simplest thing to do would be to pg_dump the existing database, load that dump into a completely new database, then add the new column. An alternative might be to use the --column-inserts option (among others) for pg_dump. That will dump data as SQL INSERT statements with explicit ...


2

Use the standard SSMS scripting feature: Right click on database in object explorer->Tasks->Generate Scripts Then choose what tables do you want to script and not forget to check "Script Data"



Only top voted, non community-wiki answers of a minimum length are eligible