tsql

[C#] how to query sql server database size

When writing C# code, if I can manage to get a SqlConnection, is there any way to perform a query to get the size of the Database? I searched the internet, seems "sp_spaceused" can be used, but it is not a table, it is a procedure. Can I query a procedure? ...

How to reference one CTE twice?

I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set. with recs as (select *, row_number() over (order by id) as rownum from ......) select * from recs where rownum between @a and @...

T-SQL: what COLUMNS have changed after an update?

OK. I'm doing an update on a single row in a table. All fields will be overwritten with new data except for the primary key. However, not all values will change b/c of the update. For example, if my table is as follows: TABLE (id int ident, foo varchar(50), bar varchar(50)) The initial value is: id foo bar ----------------- 1 ...

How to identify one-to-one (one-to-?) relationship using INFORMATION_SCHEMA or sys views in Sql Server

So here is the problem.. Using TSQL and INFORMATION_SCHEMA or sys views, how can I identify a 1:0-1 relationship, such as FK_BaseTable_InheritedTable? In a concrete example, imagine a simple DTO - FK_JoinTable_ParentTable would be rendered as a collection of JoinTable on the ParentTable object, whereas FK_BaseTable_InheritedTable woul...

The result of stored procedure to a table, without creating the table manually in SQL Server

What I need to do is, put the results of stored procedure (with lots of parameters) in to a table. Something like that: SELECT * INTO Table_1 FROM ( EXEC [MY_DataBase].[dbo].[GET_Report] '%%', '%%', '%%', 'x', 'x', 'x', 'x', 'x', 'x' .... ) X However this seems to be an incorrect syntax, I searched for it and people are first creatin...

SQL Server String parsing for special characters

I need a solution (t-sql function / procedure) to parse an SQL Server varchar(max) and eliminating all special characters and accents The output of this string will be transformed to a CSV file using an AWK script that breaks on special characters like '&', '%', '\' and all accent characters that on convert turn into unknown characters...

Inner join between same tables in Nhibernate query API

Hi I have two Domain Objects mapped right : Emails and ActionPlans the table structure is something like: create table emails(id int identity(1,1), subject nvarchar(512), parentEmailId int, rootEmailId int) create table actionPlans(id int identity(1,1), dueDate datetime, emailId int) insert into emails(subject, parentEmailId, rootE...

Keeping it simple and how to do multiple CTE in a query

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE that groups scheduled events and counts ...

SQL "transform" query

I have these data on a table (using SQL Server 2005): ID ParentID StartTime EndTime 77 62 08:00:00 11:00:00 78 62 12:00:00 15:00:00 79 62 18:00:00 22:00:00 and I want to transform it into this: ParentID BreakfastStart BreakfastEnd LunchStart LunchEnd DinnerStart DinnerEnd...

How to figure out how many tables are affected in database after inserting a record?

One third party app is storing data in a huge database (SQL Server 2000/2005). This database has more than 80 tables. How would I come to know that how many tables are affected when application stores a new record in database? Is there something available I can retrieve the list of tables affected? ...

Loading data from one table into two other tables (one detail, one summary) that are related

Suppose that I have a source table like this: Source SourceKey (PK) Data1 Data2 Amount And I am loading data from 'Source' into two related tables that look like this: Summary SummaryKey (PK) Data1 Data2 Amount Detail DetailKey (PK) SummaryKey (FK) SourceKey (FK) Data2 Amount EDI...

TRUNCATE TABLES with CONSTRAINTS

Is there a way to truncate tables with constraints ? I tried to DEACTIvATE with this: DECLARE @SQLtxt varchar(max) SET @SQLtxt = '-- DESACTIVER LES CONTRAINTES' + CHAR(10) SELECT @SQLtxt = @SQLtxt + 'ALTER TABLE [' + name + '] NOCHECK CONSTRAINT ALL;' FROM sys.tables PRINT @SQLtxt Of course, it didn't worked. I have to drop the const...

How to identify last row on a insert statement

Hello, I want to know which record is the last inserted on Detail Table B in the following order... for example If I have : 1 row Header Table A -------------------- 1 row Detail Table B 2 row Detail Table B 3 row Detail Table B 4 row Detail Table B (last) I want to do some T-SQL or run a procedure if the 4 row is inserted... Thanks i...

SQL Server -- Elegant way to use custom fields for record set.

I'm looking for an elegant way to select fields from a view, where a second supporting table defines the fields that should be selected. The view contains all of my employee data. The constraint table contains the field name, which references the view field name, and a bit which signifies to select that field. Example of View: Name ...

TSQL: Create a view that accesses multiple databases

I have a special case, for example in table ta in database A, it stores all the products I buy table ta( id, name, price ) in table tb in database B, it contain all the product that people can buy table tb( id, name, price .... ) Can I create a view in database A to list all the products that I haven`t bought? ...

How to optimize or remove redundancy from following query

I have 4 tables Table1 (employee) id name -------------------- 1 a 2 b Table2 (appointment) id table1id table3id table4id sdate edate typeid ----------------------------------------------------------------------------------- 1 1 1 1 1/1/09 NULL 100...

[TSQL] how to write a efficient to get the difference from two table

hi i am using SQL Server 2008, want to create a view to list the different between two tables. for example t1 id name ---- ------ 1 John 2 peter 3 mary 4 joe 5 sun t2 id name --- ---- 1 john 2 joe how to create a view to list all the name in t1 but not in t2. i tried to do that, and alwa...

Need group by and counting query

Suppose I have a table with threadid and itemid in it. There can be multiple entries of the itemid with the same threadid value... The query: SELECT DISTINCT THREADID, ITEMID FROM THREADS WHERE THREADID IN (SELECT DISTINCT THREADID FROM THREADS WHERE ITEMID = 10151) yields: THREADID ITEMID ----------- -------...

Which is the best for this query, "Inner join" or "Where"

I have this query, our friend "OMG Ponies" help me to make it :) :- SELECT t.id AS taskid, STUFF( ( SELECT ',' + x.tID FROM ( SELECT CAST(id AS VARCHAR(200)) AS tid FROM CRSTask c WHERE c.ParentTask = 7562 -...

TSQL INTERSECT Keyword

I have known about the SQL Server 2005/2008 keywords EXCEPT and INTERSECT for some time but have never found a compelling reason to use INTERSECT over the good old UNION keyword. Can anyone explain when or why you would use INTERSECT instead of UNION? ...