http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/ USE AdventureWorks GO SELECT t.name AS table_name ,SCHEMA_NAME(schema_id) AS schema_name ,c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name L...
When writing a SQL statement in SQL Server 2005, does the READUNCOMMITTED query hint imply NOLOCK or do I have to specify it manually too? So is: With (NoLock, ReadUnCommitted) the same as: With (ReadUnCommitted) ...
Currently, if I want to output a SQL script for a table in my database, in Management Studio, I can right click and output a create script. Is there an equivalent to output an SQL script for a database's maintenance plan?# Edit The company I work for has 4 servers, 3 servers and no sign of integration, each one running about 500,00...
We have a large database on which we have DB side pagination. This is quick, returning a page of 50 rows from millions of records in a small fraction of a second. Users can define their own sort, basically choosing what column to sort by. Columns are dynamic - some have numeric values, some dates and some text. While most sort as expe...
I need to convert a named instance of SQL server 2005, to a default instance. Is there a way to do this without a reinstall? ...
I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of internationalized data, then use nvarchar. Just use nvarchar for everything. I'm beginning to see the merits of view 2. I know that nva...
A Windows Forms application of ours pulls records from a view on SQL Server through ADO.NET and a SOAP web service, displaying them in a data grid. We have had several cases with ~25,000 rows, which works relatively smoothly, but a potential customer needs to have many times that much in a single list. To figure out how well we scale ri...
In one of my applications I have a 1gb database table that is used for reference data. It has a huge amounts of reads coming off that table but there are no writes ever. I was wondering if there's any way that data could be loaded into RAM so that it doesn't have to be accessed from disk? I'm using SQL Server 2005 ...
I want to use SQL Profiler to trace the queries executed agains my database, track performance, etc. However it seems that the SQL Profiler is only available in the Enterprise edition of SQL Server 2005. Is this the case indeed, and can I do something about it? ...
In a stored procedure, when is #Temptable created in SQL Server 2005? When creating the query execution plan or when executing the stored procedure? if (@x = 1) begin select 1 as Text into #Temptable end else begin select 2 as Text into #Temptable end ...
In a project of mine the SQL statements that are executed against a ms SQL server are failing for some unknown reason. Some of the code is already used in production so debugging it is not an easy task. Therefore I need a way to see in the database itself what SQL statements are used, as the statements are generated at runtime by the pro...
What role should I give a sql login if I need the login to be able to create a database, and create additional logins and add users based on those logins to the database i created? This is sql 2005. ...
I am looking to manage a SQL Server 2008 DB using Management Studio 2005. The reason for this is because our server is a 64-bit machine and we only have the 64-bit version of the software. Is this possible? How about managing a SQL Server 2005 DB using Management Studio 2008? ...
Hi, I've noticed that Visual Studio 2008 is placing square brackets around column names in sql. Do the brackets offer any advantage? When I hand code T-SQL I've never bothered with them. Example: Visual Studio: SELECT [column1], [column2] etc... My own way: SELECT column1, column2 etc... ...
Edit: Solved, there was a trigger with a loop on the table (read my own answer further below). We have a simple delete statement that looks like this: DELETE FROM tablename WHERE pk = 12345 This just hangs, no timeout, no nothing. We've looked at the execution plan, and it consists of many lookups on related tables to ensure no fo...
I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log? ...
I get the following error message in SQL Server 2005: User '<username>' does not have permission to run DBCC DBREINDEX for object '<table>'. Which minimum role do I have to give to user in order to run the command? ...
Just what the title says, I need to change the password for an existing sql server login and I want to do it via sql script. ...
I have a table in SQL Server that I inherited from a legacy system thats still in production that is structured according to the code below. I created a SP to query the table as described in the code below the table create statement. My issue is that, sporadically, calls from .NET to this SP both through the Enterprise Library 4 and th...
Best practices recommend not installing Sql Server to run as SYSTEM. What is the bare minumum you need to give the user account you create for it? ...