Tag Info

Hot answers tagged

21

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


12

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


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


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

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


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


6

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


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

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

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'), ...


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

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

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


4

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


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

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

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


3

This query gives you the answer you want including "NULL" values if for a given No one of the Usernames is null. SELECT t1.NO No, t1.VALUE AS PORT, t2.VALUE AS Vip1 FROM table1 t1 FULL OUTER JOIN table1 t2 ON t1.NO = t2.NO WHERE t1.USERNAME = 'Port' AND t2.USERNAME = 'Vip1'; On the table: SQL> select * from table1 where ...


3

One way to get this done is probably something you have already done, and that is to replace your line: if @DebugMode=1 print @SQL with if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar) And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors. You could also use ...


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

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



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