I have to import a number of text files into SQL Server tables, and some of the text files have columns where all the rows appear to be mostly, if not all, null values.  Is there any simple way to write a query that will list all the columns in a table that have nothing but null values?
124269
How do I select all the columns in a table that only contain NULL values for all the rows?  I'm using MS SQL Server 2005.  I'm trying to find out which columns are not used in the table so I can delete them.
9594
I had this issue long time ago, I found a workaround which suited me and forgot about it.  But now there's  on SO so I'm willing to bring this problem up.  There's a view that joins few tables in a very straightforward way (orders + order lines).    When queried without a where clause, the view returns several million lines. However, noone ever calls it like that. The usual query is  select * from that_nasty_view where order_number = 123456;   This returns about 10 records out of 5m.  An important thing: the view contains a window function, rank(), which is partitioned exactly by the field using which the view is always queried:  rank() over (partition by order_number order by detail_line_number)   Now, if this view is queried with literal parameters in the query string, exactly as shown above, it returns the rows instantly. The execution plan is fine:   Index seek on both tables using the indices on order_number (returns 10 rows). Calculating windows over the returned tiny result. Selecting.   However, when the view is called in a parametrized way, things get nasty:   Index scan on all tables ignoring indices. Returns 5m rows. Huge join. Calculating windows over all partitions (about 500k windows). Filter to take 10 rows out of 5m. Select   This happens in all cases when parameters are involved. It can be SSMS:  declare @order_number int = 123456; select * from that_nasty_view where order_number = @order_number;   It can be an ODBC client, such as Excel:  select * from that_nasty_view where order_number = ?   Or it can be any other client that uses parameters and not sql concatenation.  If the window function is removed from the view, it runs perfectly quickly, regardless of whether or not it's queried with parameters.  My workaround was to remove the offending function and reapply it at a later stage.  But, what gives? Is it genuinely a bug in how SQL Server 2008 handles window functions?
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
84166
I am using QGIS. I have an attributetable with several columns. I want to change the values of column1 if they are NULL with the values of the same row in column5. case WHEN column1 is NULL THEN  END  How do I say &quot;use the value from the same row&quot;?
103771
How do I identify which features have NULL attributes in the attribute table of a shapefile?  When i try [field]= 'NULL' it selects the features with value = 0 (see picture)
50860
I have a Shapefile with a lot of empty columns but many of them are empty. Using QGIS or ogr2ogr, how do I remove all attribute columns where everything is NULL?
78798
I have a table structured as so (Simplified)  Name, EMail, LastLoggedInAt   I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null.   It looks like I can do this? Is it possible?
57994
I've got data in SQL Server 2005 that contains HTML tags and I'd like to strip all that out, leaving just the text between the tags. Ideally also replacing things like &amp;lt; with &lt;, etc. Is there an easy way to do this or has someone already got some sample T-SQL code? I don't have the ability to add extended stored procs and the like, so would prefer a pure T-SQL approach (preferably one backwards compatible with SQL 2000). I just want to retrieve the data with stripped out HTML, not update it, so ideally it would be written as a user-defined function, to make for easy reuse. So for example converting this: &lt;B&gt;Some useful text&lt;/B&gt;&amp;nbsp; &lt;A onclick=&quot;return openInfo(this)&quot;    href=&quot;http://there.com/3ce984e88d0531bac5349&quot;    target=globalhelp&gt;    &lt;IMG title=&quot;Source Description&quot; height=15 alt=&quot;Source Description&quot;          src=&quot;/ri/new_info.gif&quot; width=15 align=top border=0&gt; &lt;/A&gt;&amp;gt;&amp;nbsp;&lt;b&gt;more text&lt;/b&gt;&lt;/TD&gt;&lt;/TR&gt;  to this: Some useful text &gt; more text
21671
I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations and I have the following:  TABLE office_settings column office_id (bigint not null) column setting_value (NVARCHAR(500) NOT NULL)  office_id    setting_value -------------------------------------------- 4             0.2 4             0.2 4             2 4             3 5             0.2 5             0.2 5             2 5             3   I'd like to convert this using PIVOT (assuming that's the right tool for the job) to this, basically "grouping by" the office_id column, so:  office_id    value_1    value_2    value_3   value_4 ------------------------------------------------------     4         0.2         0.2         2         3     5         0.2         0.2         2         3   I should also mention that I need to CAST the setting_value column values to DECIMAL(4,2) in the process of this - in case that has any bearing on the T-SQL required to accomplish it.  Any ideas?
123269
Inspired by a StackOverflow question ().  Is there a DBMS that actually supports the = NULL syntax?
8920
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?
29132
I'm looking at creating a basic ORM (purely for fun), and was wondering, is there a way to return the list of tables in a database and also the fields for every table?  Using this, I want to be able to loop through the result set (in C#) and then say for each table in the result set, do this (e.g. use reflection to make a class that will do or contain xyz).  Further to this, what are some good online blogs for SQL Server? I know this question is really about using system SPs and databases in Sql Server, and I am ok with general queries, so I'm interested in some blogs which cover this sort of functionality.  Thanks
44222
I would like to search an entire MS SQL 2000 database for one value. This would be to aid development only. Keep that in mind when considering this question.   This will get all the table names and the column of the data type I'm looking for:  SELECT Columns.COLUMN_NAME, tables.TABLE_NAME FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = 'INT'     I was thinking something like this:  -- Vars DECLARE @COUNTER INT DECLARE @TOTAL INT DECLARE @TABLE CHAR(128) DECLARE @COLUMN CHAR(128) DECLARE @COLUMNTYPE CHAR(128) DECLARE @COLUMNVALUE INT  -- What we are looking for SET @COLUMNTYPE = 'INT' SET @COLUMNVALUE = 3 SET @COUNTER = 0  -- Find out how many possible columns exist SELECT @TOTAL = COUNT(*)   FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = @COLUMNTYPE PRINT CAST(@TOTAL AS CHAR) + 'possible columns' WHILE @COUNTER &lt; @TOTAL BEGIN     SET @COUNTER = @COUNTER +1     -- ADD MAGIC HERE END   Any ideas?  UPDATE I recently found this  that works quite well.
108476
I have the following SQL Server 2008 table and data:  CREATE TABLE dbo.MyTable (Sno int, refid int);  INSERT INTO dbo.MyTable VALUES   (17, 8),   (16, 8),   (15, 7),   (14, 7),   (13, 6),   (12, 6),   (11, 5),   (10, 5),   (9, 4),   (8, 4),   (7, 3),   (6, 3),   (5, 2),   (4, 2),   (3, 1),   (2, 1),     (1, 0);   Note that Sno 2 and 3 are referencing Sno 1 in the same table (self-reference). So 2 and 3 are children of 1 and 1 is the parent of 2 and 3.  Requirement  I will pass in a single Sno value (e.g. 4) and I need:   All the parents of that Sno in a result set. All the children of that Sno in another result set.   I need to display the top 10 levels for each result set.  This illustrates the relationships and shows what I have tried so far:  SELECT refid FROM dbo.MyTable  WHERE Sno = (SELECT refid FROM dbo.MyTable WHERE Sno = 4); GO SELECT refid FROM dbo.MyTable WHERE Sno = 4; GO Select Sno FROM dbo.MyTable  WHERE refid = (SELECT refid FROM dbo.MyTable WHERE Sno = 4); GO
12741
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?  In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.  For example  such an .
799
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.  The  say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even  to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).  As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.  It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?
45538
Let's say I have a table of employees, like this:  Name     Sex     Role  Bob      M       Developer Joe      M       QA   Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:  Name     Sex     Role  Bob      M       Developer Bob      M       Janitor Joe      M       QA Joe      M       CEO   And will output the following rows:  Name     Role  Bob      Developer, Janitor Joe      QA, CEO   It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.  Using SQL Server 2008 if it matters.