Right click the Database and select Tasks -> Generate Scripts. You can you then select all the objects you require or only certain objects. There are some Script Options that you should look at: Script USE DATABASE – You should set this to false if you are creating a new database with a different name. … Read more
I just want to call out couple of points: Use code generators You can’t have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation … Read more
Here is the sample query to update table with text column using REPLACE function. Hope this is useful for you. UPDATE <Table> set textcolumn= REPLACE(SUBSTRING(textcolumn,1,DATALENGTH(textcolumn)),’findtext’,’replacetext’) WHERE <Condition>
For clarity………. Executing sp_recompile will “mark” the given stored procedure for recompilation, which will occur the next time it is executed. Using the WITH RECOMPILE option will result in a new execution plan being generated each time the given stored procedure is executed. To clear the entire procedure cache execute DBCC FREEPROCCACHE
If you’re using SQL Server 2008 (won’t work for earlier version) there is the concept of a filtered index. You can create the index on a filtered subset of the table. CREATE UNIQUE INDEX indexName ON tableName(columns) INCLUDE includeColumns WHERE columnName IS NOT NULL
I enabled remote errors to pinpoint the problem. I identified that a column in a particular dataset (one of my views) was throwing an error. So using a tool “SQL Delta”, I compared the development version of the database with the live version on the reporting server. I noticed that one of the views had … Read more
The difference between Union and Union all is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set. If you select Distinct from Union All result … Read more
That depends on what you’re doing: If speed is the primary concern then a plain old int is probably big enough. If you really will have more than 2 billion (with a B 😉 ) records, then use bigint or a sequential guid. If you need to be able to easily synchronize with records created … Read more
Should be in Program Files>Microsoft SQL Server>MSSQL 1.0>MSSQL>BACKUP> In my case it is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup If you use the gui or T-SQL you can specify where you want it T-SQL example BACKUP DATABASE [YourDB] TO DISK = N’SomePath\YourDB.bak’ WITH NOFORMAT, NOINIT, NAME = N’YourDB Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 … Read more
With the table valued function you generally use Cross Apply. Select * From myTable m CROSS APPLY fn_function(m.field1, m.field2)