Browse by Tags

All Tags » sql server » tables   (RSS)
Getting table name from column name
15 September 07 05:23 AM | SQL Master | 2 Comments   
If you know the name of one column you want to find from which table that is originated, to retrieve the table name you can use following TSQL: SELECT CASE (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS AS c2 WHERE c2.TABLE_NAME = c1.TABLE_NAME AND Read More...
TSQL to find all triggers in a database including their count
23 August 07 01:28 AM | SQL Master | 2 Comments   
For SQL Server 2000 version: SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete' WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' , 'S1',s1.*,'S2',s2.* FROM sysobjects Read More...
TSQL to generate a script that updates a column on every table in my database. How do I do this?
29 July 07 01:39 AM | SQL Master | 2 Comments   
SELECT 'ALTER TABLE ' + sysobjects.name + ' ' + 'ALTER COLUMN ' + syscolumns.name + ' ' + systypes.name + '(' + cast(syscolumns.length as varchar) + ') ' + 'COLLATE ' + syscolumns.collation + ' ' + case when syscolumns.isnullable = 1 then 'NULL' else Read More...
TSQL to generate blocking scenario for testing
27 July 07 12:44 AM | SQL Master | 1 Comments   
Most of the times you have observed to identify the blocking and how to resolve them. How about you need a script to generate a blocking scenario within your queries, this is to identify the blocker script is working or not. Also will help to test whether Read More...
How do I find all the tables and indexes that are partitioned?
17 July 07 02:42 AM | SQL Master | 1 Comments   
If you have a huge number of partitioned tables and indexes then the following query would help you to identify the list of objects that are involved. USE <database_name>; GO SELECT SCHEMA_NAME(o.schema_id) AS schema_name ,OBJECT_NAME(p.object_id) Read More...
TSQL to return (display) all tables row count in database - think about performance
16 July 07 02:05 AM | SQL Master | 1 Comments   
In the forums and newsgroups this is a very common question that how to get all the tables rowcount or to get optimum values. You may be aware using SELECT COUNT(*) statement, but be aware it will make full table scan to return the rowcount and think Read More...