Generate script of all indexes, keys in the SQL Server database

I am looking to generate scripts like: ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID); CREATE INDEX IX_EVT_EVENTS on EVT_EVENTS(prg_event_t1, prg_event_t2, num_order) The main problem I cannot find all information that I need, for example this query: SELECT TableName, I.* FROM sys.indexes I Inner Join sys.tables T on i.object_id=t.object_id returns information about […]

Does SQL Server guarantee sequential inserting of an identity column?

In other words, is the following “cursoring” approach guaranteed to work: retrieve rows from DB save the largest ID from the returned records for later, e.g. in LastMax later, “SELECT * FROM MyTable WHERE Id > {0}”, LastMax In order for that to work, I have to be sure that every row I didn’t get […]

Entity Framework constrains on navigation properties

I want to limit model that are returned by a navigational property. For example, I am using an AuditInfo model to log the activity of a model. Once a model is deleted the DeletedBy and Deleted attributes are set. However since nothing is ever really “deleted” from the database, these models will still be populated […]

size limit for XML datatype in SQL 2005

Is there a size limit on the XML data type in SQL 2005? When I try to return anything more than 44kb size of the XML string from my stored proc, it just returns an empty string. I am using FOR XML PATH to return hierarchical data sets in XML format from my stored procs.

T-SQL code to get a DateTime contain only Month and Year from any DateTime

Given a table Event containing a field called EventTime of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month. The resulting type of the Group By field must also be Date Time with a 0 time […]

Retrieving XML element name using t-SQL

If I have: <quotes> <quote> <name>john</name> <content>something or other</content> </quote> <quote> <name>mary</name> <content>random stuff</content> </quote> </quotes> How do I get a list of the element names ‘name’ and ‘content’ using T-SQL? The best I’ve got so far is: declare @xml xml set @xml = … select r.value(‘quotes/name()[1]’, ‘nvarchar(20)’ as ElementName from @xml.nodes(‘/quotes’) as records(r) But, […]

login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

so make quick: i use windows 7 ultimate. I can login with windows authentication mode but sql authentication with user ‘sa’ don’t work!. sa user is enable so what do i do?

ALTER INDEX failed because of QUOTED_IDENTIFIER when running from sp_msForEachTable

When I try to rebuild an index on a table: ALTER INDEX ALL ON [dbo].[Allocations] REBUILD that works fine. But when I call EXECUTE sp_msForEachTable ‘ALTER INDEX ALL ON ? REBUILD’ I reach the same same table, and it fails with: Msg 1934, Level 16, State 1, Line 2 ALTER INDEX failed because the following […]

How to check my data in SQL Server have carriage return and line feed?

This question already has an answer here: SQL query for a carriage return in a string and ultimately removing carriage return 8 answers

How much is the network – determing network overhead in SQL Server

We have a dev server running C# and talking to SQL server on the same machine. We have another server running the same code and talking to SQL server on another machine. A job does 60,000 reads (that is it calls a stored procedure 60,000 times – each read returns one row). The job runs […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.