How can I delete all function from my database using sql-script.  I tried:  declare @nm varchar(100) select @nm = name from sysobjects where type = 'FN' exec('drop function ' + @nm)   And I put in into the loop but it not delete all function. Please help
123840
Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one SQL statement?  REASON FOR REQUEST:  I want to have a DB script for cleaning up an existing DB which is not in use rather than creating new ones, especially when you have to put in a request to your DB admin and wait for a while to get it done!
117105
A query that is used to loop through 17 millions records to remove duplicates  has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back?  I have found that when I do a    select count(*) from myTable   That the rows that it returns (while doing this query) is about 5 less than what the starting row count was. Obviously the server resources are extremely poor, so does that mean that this process has taken 16 hours to find 5 duplicates (when there are actually thousands), and this could be running for days?  This query took 6 seconds on 2000 rows of test data, and it works great on that set of data, so I figured it would take 15 hours for the complete set.  Any ideas?  Below is the query:  --Declare the looping variable DECLARE @LoopVar char(10)       DECLARE      --Set private variables that will be used throughout       @long DECIMAL,       @lat DECIMAL,       @phoneNumber char(10),       @businessname varchar(64),       @winner char(10)      SET @LoopVar = (SELECT MIN(RecordID) FROM MyTable)      WHILE @LoopVar is not null     BEGIN        --initialize the private variables (essentially this is a .ctor)       SELECT          @long = null,         @lat = null,         @businessname = null,         @phoneNumber = null,         @winner = null        -- load data from the row declared when setting @LoopVar         SELECT         @long = longitude,         @lat = latitude,         @businessname = BusinessName,         @phoneNumber = Phone       FROM MyTable       WHERE RecordID = @LoopVar        --find the winning row with that data. The winning row means        SELECT top 1 @Winner = RecordID       FROM MyTable       WHERE @long = longitude         AND @lat = latitude         AND @businessname = BusinessName         AND @phoneNumber = Phone       ORDER BY         CASE WHEN webAddress is not null THEN 1 ELSE 2 END,         CASE WHEN caption1 is not null THEN 1 ELSE 2 END,         CASE WHEN caption2 is not null THEN 1 ELSE 2 END,         RecordID        --delete any losers.       DELETE FROM MyTable       WHERE @long = longitude         AND @lat = latitude         AND @businessname = BusinessName         AND @phoneNumber = Phone         AND @winner != RecordID        -- prep the next loop value to go ahead and perform the next duplicate query.       SET @LoopVar = (SELECT MIN(RecordID)      FROM MyTable     WHERE @LoopVar &lt; RecordID)     END
101544
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 somehow?  I want to create a script that includes the schema and data of the entire data base.  Tools like ScriptDB and sqlpubwiz.exe all seem to target SQL Server 2005. What about SQL Server 2012?
40918
I have a string coming from a table like "can no pay{1},as your payment{2}due on {3}". I want to replace {1} with some value , {2} with some value and {3} with some value .  Is it Possible to replace all 3 in one replace function ? or is there any way I can directly write query and get replaced value ?   I want to replace these strings in Oracle stored procedure the original string is coming from one of my table I am just doing select on that table   and then I want to replace {1},{2},{3} values from that string to the other value that I have from another table
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
45839
Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?  Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here   Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees and I wrote the following script to execute a select statement on all of them:  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp  select * into #tmp from Database1.dbo.Table1 where 1=0 DECLARE @statement nvarchar(max) =    N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'  DECLARE @LastDatabaseID INT SET @LastDatabaseID = 0  DECLARE @DatabaseNameToHandle varchar(60) DECLARE @DatabaseIDToHandle int  SELECT TOP 1 @DatabaseNameToHandle = Name, @DatabaseIDToHandle = Database_Ref_No FROM Databasees WHERE Database_Ref_No &gt; @LastDatabaseID ORDER BY Database_Ref_No  WHILE @DatabaseIDToHandle IS NOT NULL BEGIN    DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'   EXEC @sql @statement    SET @LastDatabaseID = @DatabaseIDToHandle   SET @DatabaseIDToHandle = NULL    SELECT TOP 1 @DatabaseNameToHandle = Name,   @DatabaseIDToHandle = Database_Ref_No   FROM Databasees   WHERE Database_Ref_No &gt; @LastDatabaseID   ORDER BY Database_Ref_No END  select * from #tmp DROP TABLE #tmp   However the script above fails with the following message:     An explicit value for the identity column in table '#tmp' can only be   specified when a column list is used and IDENTITY_INSERT is ON.   Adding this:  SET IDENTITY_INSERT #tmp ON   does not help, since, I can't specify the column list and keep it generic.  In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.  So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.  Can anyone think of a relatively easy way to achieve what I want?
83690
What is the best way to version control my database objects? I'm using Visual studio 2005/2008 and SQL server 2005. I would prefer a solution which can be used with SVN.
26567
How can I execute a query that connects to different databases?  I have the following query, and I need to rewrite it for Drupal.  SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b WHERE a.empid = b.empid   How can I rewrite the query using db_query(), or db_select()?
42242
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.  Ex: "one,two,three,four" would return a new table with only one column containing the following values:  one two three four   Does this code look error prone to you guys? When I test it with   SELECT * FROM utvf_Split('one,two,three,four',',')    it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.  Here is the function:  USE *myDBname* GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)  RETURNS @SplitValues TABLE (     Asset_ID VARCHAR(MAX) NOT NULL )  AS BEGIN             DECLARE @FoundIndex INT             DECLARE @ReturnValue VARCHAR(MAX)              SET @FoundIndex = CHARINDEX(@delimiter, @String)              WHILE (@FoundIndex &lt;&gt; 0)             BEGIN                   DECLARE @NextFoundIndex INT                   SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)                   SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)                   SET @FoundIndex = CHARINDEX(@delimiter, @String)                   INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)             END              RETURN END
47018
I use the following command in order to delete only the files that start with DBG and older then two day , but this syntax not print the files that deleted  find /tmp  -type f -mtime +2 -name "DBG*" -exec rm {} \;   How to add to this the find syntax , the print in order to print the deleted files?
30126
Currently I deleted one table from the database. But now I want some of the information from that table.   Please suggest how to restore or get back the table.  I am using SQL Server 2008 R2.
66399
I have the following code:  const string Sql =      @"select distinct [name]        from tblCustomers        left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId         where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", searchString);     ... }   This does not work, I tried this as well:  const string Sql =      @"select distinct [name]       from tblCustomers       left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId        where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");     ... }   but this does not work as well. What is going wrong? Any suggestions?
68032
I have a table item having just one column name:-  name ----- toys shirt mobile Shirt speaker Toys ....  ....   I am trying to delete duplicate rows except one duplicated record with the help of below query  DELETE FROM ( SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,  @num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM  item ORDER BY `name`) AS result1 ) AS result  WHERE rownumber &gt;1   The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.  I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.
87503
I always read that it is recommended to set objects to nothing, once I am done with them. But I normally use them only in functions inside forms.  Isn't the reference lost and memory released when the function scope is left, regardless of setting objects to Nothing?   i.e. is it really necessary to do:  Set db = Nothing Set record_set = Nothing
27084
I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole.  I have found a code sample that can collect disk space information per database.  IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL     DROP TABLE disk_activity  CREATE TABLE disk_activity (        servername NVARCHAR(100)     ,  database_id INT PRIMARY KEY     , name NVARCHAR(MAX)     , data_used_size DECIMAL(18,2)     , log_used_size DECIMAL(18,2) )  DECLARE @SQL NVARCHAR(MAX)  SELECT @SQL = STUFF((     SELECT '     USE [' + d.name + ']     INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)     SELECT           (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME         , DB_ID()         , DB_NAME()         , SUM(CASE WHEN [type] = 0 THEN space_used END)         , SUM(CASE WHEN [type] = 1 THEN space_used END)     FROM (         SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)         FROM sys.database_files s         GROUP BY s.[type]     ) t;'     FROM sys.databases d     WHERE d.[state] = 0     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')  EXEC sys.sp_executesql @SQL   However, I would like to collect the metrics across the entire server, i.e. total disk space allocated for all databases, total disk space used, total disk space free, and percentage of disk space used.  How should I modify the above script or are there other options available?
83825
How does one remove a field from a node programmatically? I have a migration in hook_update_N that moves the contents from a a field into a custom table. After that migration, I want to remove the field in that same function.  Are there any field API's that cater removing fields?  Edit, Solution: Because the answers lack actual code, here is what I did to move fields from the $users into my own records and subsequently remove the field from the database;  function my_module_update_7005(&amp;$sandbox) {   $slice = 100;   //Fetch users from database;   if (!isset($sandbox['progress'])) {     $sandbox['progress'] = 0;     $sandbox['current_uid'] = 0;     // We'll -1 to disregard the uid 0...     $sandbox['max'] = db_query('SELECT COUNT(DISTINCT uid) FROM {users}')-&gt;fetchField() - 1;   }   if (empty($users)) {     $sandbox["current_uid"] += $slice;   }   $users = db_select('users', 'u')     -&gt;fields('u', array('uid', 'name'))     -&gt;condition('uid', $sandbox['current_uid'], '&gt;')     -&gt;range(0, $slice)     -&gt;orderBy('uid', 'ASC')     -&gt;execute();   //Loop trough users;   foreach ($users as $user) {     $foo = new Foo();     // Warning: drupal's fields return mixed values; e.g. NULL versus an int.     $foo-&gt;debits = (int) $user-&gt;user()-&gt;field_credits["und"][0]["value"];     $foo-&gt;save();      $sandbox['progress']++;     $sandbox['current_uid'] = $user-&gt;uid;   }    $sandbox['#finished'] = empty($sandbox['max']) ? 1 : ($sandbox['progress'] / $sandbox['max']);    // Remove the field.   field_delete_field("field_credits"); //note that the name for Foo is field_foo   field_purge_batch($sandbox['max']+1);//Drupal seems to have an offbyone problem. }
53365
We all know that to select all columns from a table, we can use  SELECT * FROM tableA   Is there a way to exclude column(s) from a table without specifying all the columns?  SELECT * [except columnA] FROM tableA   The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns.