Tag Info

Hot answers tagged

13

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other table, view, sequence, user-defined composite type or index 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 of the index, you can ...


11

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


10

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

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

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

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

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


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

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

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

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

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

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

You can find the code for your stored procedures in sys.sql_modules. You can write a query on them like this: SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) FROM sys.sql_modules WHERE [definition] LIKE '%UPDATE YourTable%' AND [definition] NOT LIKE '%Bar = @Bar%' Be warned that this assumes that your code looks EXACTLY like this. So ...


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

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


3

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement. If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that ...


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

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

To elaborate on gbn's answer, two sprocs is the better way to go here; additionally, I'd go one step further and place the query (sans top clause) in a table-valued function and then have the sprocs be nothing more than a shell which calls the TVF and applies the top clause appropriately (by number of rows or percent). The only caveat here being that if you ...


3

This query on the system catalog creates the necessary DDL scipt: SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n') FROM pg_namespace WHERE nspname LIKE 'ceu_shard_test_merge_%'; Note the use of format() to escape identifiers if necessary. format() requires PostgreSQL 9.1+. Replace with quote_ident() in older versions. string_agg() ...


3

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


3

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking. http://msdn.microsoft.com/en-us/library/bb630284.aspx



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