Browse by Tags

All Tags » sql server » table (RSS)
How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?
Published 20 May 8 3:12 AM | SSQA.net
It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored procedures from a SQL Server database within your environment, but it will be handy to have such a code at your end when such task is required. There are 2 ways...
Filed under: , , , , , , ,
TSQL to change collation of database, whats new in SQL 2008 then?
Published 12 March 8 1:24 PM | SSQA.net
Cross post from my BlogCasts site....( read more ) Read More...
Filed under: , , , , ,
Different ways to know structure of a table
Published 28 February 8 2:51 AM | SSQA.net
You can use one of the following to know the structure of a table 1 Generate SQL Script option from Enterprise Manager/Management Studio 2 select * from information_schema.columns where table_name='table_name' 3 EXEC sp_help 'table_name'...
Filed under: , , , , ,
SQL Server OLAP - back to basics for Cube access from front-end application?
Published 11 February 8 3:38 AM | SSQA.net
One of the [SSP] forum user asked about what is OLAP, how the process flow occurrs when update happens to a fact table! It sounds like the basic question for Analysis Services user, OLAP - On-Line Analytical Processing for BI - Business Intelligence is...
Filed under: , , , , , , , , , , ,
SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?
Published 1 February 8 6:30 AM | SSQA.net
As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do...
Filed under: , , , , , , , ,
You may receive an error message when you try to export a table by using the SQL Server Import and Export Wizard
Published 18 November 7 3:15 PM | SSQA.net
The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New and Major Modifications...
Filed under: , , , , , , ,
FIX: An empty string is replicated as a NULL value when you synchronize a table to a SQL Server 2005 Compact Edition subscriber
Published 16 November 7 3:5 PM | SSQA.net
The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New and All Modifications FIX: An empty string is replicated as...
Filed under: , , , , , ,
Internal Tables in SQL Server 2005, what are they for?
Published 2 November 7 3:17 AM | SSQA.net
By default SQL Server automatically creates the internal tables for Full-text search, XML indexes and Service broker. These are also created when a user query is written poorly that uses tempdb heavily. At they are called internal tables but do not contain...
Filed under: , , , , , , , ,
How to get rowcount efficiently within a SSIS package?
Published 5 September 7 3:50 AM | SSQA.net
I would like to call this as how efficiently you can obtain counting rows in SSIS package. Usual thought would come is @@rowcount or COUNT_BIG functions, but this is not that efficient way to obtain the results. SSIS provides the snippet within its components...
Filed under: , , , , , , ,
DBCC EXTENTINFO - unused pages in these extents (undocumented)
Published 4 September 7 7:45 AM | SSQA.net
When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when...
Filed under: , , , , , , , , , ,
Triggers within CLR - advantage over TSQL
Published 4 September 7 4:10 AM | SSQA.net
You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested...
Filed under: , , , , , , , ,
Performance - check whether it is affecting application or system, don't blame SQL Server alone
Published 3 September 7 9:42 AM | SSQA.net
Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL...
Filed under: , , , , , , , , , ,
Failed to claim unused space with SP_SPACEUSED?
Published 31 August 7 4:9 AM | SSQA.net
Recently I have been stumped by not cliaming unused space correctly even though after delete handful of rows on a table. You may be aware using SP_SPACEUSED will get you details such as : Column name Data type Description database_name nvarchar(128) Name...
Filed under: , , , , , , , ,
Rebuild some fragmented indexes - problem with Extent Scan Fragmentation value
Published 24 August 7 6:24 AM | SSQA.net
This was the question from SSP forums, I’m trying to rebuild few fragmented indexes on a table that is updated on regular basis. The “dbcc showcontig” gave this result: Table: 'Confidential' (999999999); index ID: 5, database ID: 5 LEAF level...
Filed under: , , , , , , , ,
FIX: Error message when you try to edit a SQL Server Agent job or a maintenance plan by using SQL Server Management Studio in SQL Server 2005: "String or binary data would be truncated"
Published 23 August 7 3:17 PM | SSQA.net
The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition, SQL Server 2005 Enterprise X64 Edition & SQL Server 2005 Enterprise Edition Notification Contents: New and All Modifications FIX...
Filed under: , , , , , , ,
Error: Could not find row in sysindexes for database. 8966, 823 and 602
Published 14 August 7 4:35 AM | SSQA.net
Long ago, not long ago.... No doubt that many of you might have gone through the error above within your SQL environment, also I see many forum posts out there to resolve the issue. The bottom line of this issue is Hardware and no other issue can contribute...
Filed under: , , , , , , , , , , ,
Table fragmentation, Index contention and locking issues to resolve
Published 31 July 7 9:31 AM | SSQA.net
Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the a rticle about DetectTableFragmentation in both 2000 and 2005 version. Fragmentation occurs due to...
Filed under: , , , , , , , , ,
How do I find all the statistics and statistics columns on a specified object?
Published 16 July 7 7:46 AM | SSQA.net
USE <database_name>; GO SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON...
Filed under: , , , , , ,
Capitals or small letters - which one you choose when you are writing TSQL code?
Published 13 July 7 9:25 AM | SSQA.net
Have you ever gave a thought about writing a code within your development environment, I'm talking about writing TSQL scripts and not going for programming languages such as C# or ASP.net. So what is your favourite in this case: Look at this SQLBlog...
Filed under: , , , ,
FIX: Error messages when you run a query against a linked server that you create in SQL Server 2005: "Statement(s) could not be prepared" and "The column prefix '<column_prefix_name>' does not match with a table name or alias name used
Published 27 June 7 11:52 AM | SSQA.net
Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition, SQL Server 2005 Enterprise X64 Edition & SQL Server 2005 Enterprise Edition Notification Contents: New and All Modifications FIX: Error messages when you run a query against...
Filed under: , , , , , ,
More Posts Next page »