Tag Info

Hot answers tagged

30

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name ...


13

You can use nested EXEC calls. The database context changed by the USE persists to the child batch. DECLARE @DB SYSNAME SET @DB = 'tempdb' DECLARE @CreateViewStatement NVARCHAR(MAX) SET @CreateViewStatement = ' USE '+ QUOTENAME(@DB) +'; EXEC('' CREATE VIEW [dbo].[MyTable] AS SELECT 1 AS [Foo] '') ...


11

The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns. Implementation details mean the buffered output is reversed. Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect. IIRC ...


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 ...


10

No. There is no flag or metadata about "UsesDynamicSQL" You have to search the definition... There are 2 ways to execute dynamic SQL sp_executesql EXEC (.. or EXECUTE (' You can search for the first in sys.sql_modules, the 2nd using LIKE WHERE REPLACE(definition, ' ', '') LIKE '%EXEC(%' OR REPLACE(definition, ' ', '') LIKE '%EXECUTE(%' OR ...


8

Strict DRY doesn't really apply to databases I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance. Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL ...


8

Ok, couple of things. always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the only statement in the batch (and that will not the case here). always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have. always use ...


7

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it ...


7

Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it. Since you will be ...


7

Try the following code: CREATE TABLE #Names ( [Type] VARCHAR(50), ColNum SMALLINT, ColName VARCHAR(50), ColDataType VARCHAR(20) ) INSERT INTO #Names VALUES ('Customer', 1, 'CustomerID', 'INT'), ('Customer', 2, 'CustomerName', 'VARCHAR(50)'), ('Customer', 3, 'CustomerJoinDate', 'DATE'), ('Customer', 4, 'CustomerBirthDate', 'DATE'), ...


7

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause. CREATE VIEW tablesviews AS SELECT columns, 'TABLE1' as tablename FROM Table1 UNION ALL SELECT columns, 'TABLE2' as tablename FROM Table2 ... ...


7

I am curious to know how the column names getting concatenated. Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes. The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it. From SET @local_variable (Transact-SQL) Do ...


6

Try this: SELECT DISTINCT OBJECT_NAME([object_id]) FROM sys.sql_dependencies AS d WHERE referenced_major_id = OBJECT_ID('dbo.YourTable') AND is_updated = 1 AND NOT EXISTS ( SELECT 1 FROM sys.sql_dependencies AS id INNER JOIN sys.columns AS c ON id.referenced_major_id = c.[object_id] AND id.referenced_minor_id = c.column_id WHERE id.object_id ...


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 ...


6

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required. As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the ...


5

Given the new information laid out in the comments, there are at least three solutions to this that don't require this mess of dynamic SQL and manual, after the fact reaction: use a ruler Seriously, he's a DBA with job responsibilities at your company. Surely you can implement a policy that tables have clustered indexes except in certain scenarios (and in ...


5

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time. The only option is to use dynamic SQL, with all the blessings and pitfalls that follow. You could ...


5

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance. I don't think ...


5

Dynamic SQL is a tool. And as a tool have some applications - for administrative works is a blessing, for example. Not so good on SP used by applications, specially if you didn't manage the parametrization of the generated code(latest versions of SQL Server reduced the problems, but still valid). I won't enter in detail here, so I'll recommend an ...


5

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE. Here is a demo how to get around this: CREATE TYPE mytype AS (id int, txt text); DO $body$ DECLARE _val mytype := (1, NULL)::mytype; _name text := 'txt'; ...


5

I'm not sure that dynamic SQL is the only answer here, though sometimes it can be the best solution if your search parameters get relatively complex. In the simple case you propose, why not a simple query that accepts one or both parameters... WHERE UserID = COALESCE(@User, UserID) AND UserName LIKE COALESCE(@Name+'%', UserName) But you will have to ...


5

You can do something along the following lines: test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer) RETURNS SETOF integer AS $body$ BEGIN CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS SELECT a FROM generate_series(1, cnt) t(a); IF (SELECT count(1) FROM tmp_container) > 5 THEN RETURN QUERY SELECT a FROM ...


5

Another method would be to use COLLATE DATABASE_DEFAULT vs specifying the collation explicitly. DECLARE @sql NVARCHAR(MAX) DECLARE @ParamDefinition NVARCHAR(MAX) DECLARE @log NVARCHAR(MAX) ,@vCrlf CHAR(2); SELECT @log = '' ,@vCrlf = CHAR(13)+CHAR(10); SELECT @sql = ' --========================================== -- ...


4

In addition to what @filiprem wrote, here is how you do this properly: ... DECLARE tbl_var text := 'myTable'; -- I would not use mixed case names .. BEGIN EXECUTE ' CREATE TABLE ' || quote_ident(tbl_var) || '( foo integer NOT NULL, bar text NOT NULL)'; ... Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with ...


4

Try dispensing with the explicit cursor: begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create table foo(id serial); insert into foo default values; create or replace function truncate_tables(username in varchar) returns void as $$ declare r record; begin for r in (select tablename ...


4

This particular example can be simpler. You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement: CREATE OR REPLACE FUNCTION truncate_tables(_username text) RETURNS void AS $func$ BEGIN EXECUTE ( SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(t.tablename), ', ') || ' CASCADE;' ...


4

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL. An IF statement in one stored proc would be enough too


4

I think you meant to use sp_executesql: EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement: SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ...


4

For simple SQL statements, such as the one you're describing, there is indeed an overall hit on performance in an Oracle DB. Since the DBMS must soft parse the sql each time (which also takes up a little bit of time), it then needs to write to SGA and might end up taking away resources from already parsed execution plans from static queries, since the SGA is ...


4

This sounds prime for a front end display solution. Query 1 would pull back your data, Query 2 would pull back the column names and in code when you build what ever structure you use to display you set the headers from the second query. While a Pure SQL Method may be possible it will be dynamic SQL and code maintnence would be a nightmare. Also your ...



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