Browse by Tags

All Tags » 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...
Display the server-level information the application role can currently view.
09 August 07 03:28 AM | SQL Master | 2 Comments   
SELECT sid, status, name, dbname, hasaccess, loginname FROM master.dbo.syslogins; GO SELECT spid, kpid, lastwaittype, waitresource, dbid FROM master.dbo.sysprocesses; GO Read More...
List tables that do not have primary keys
02 August 07 04:35 AM | SQL Master | 2 Comments   
SELECT SCHEMA_NAME(schema_id) AS schema_name ,name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 ORDER BY schema_name, table_name; GO About the above function OBJECTPROPERY, BOL refers: This function cannot be Read More...
Filed under: , ,
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...
Retrieve SessionID and batch information
24 July 07 02:37 AM | SQL Master | 1 Comments   
You may be aware using SP_WHO or SP_WHO2 statements you can get information about a session id and statement that is running since good olden days of SQL Server. Within SQL 2005 version you can take help of TSQL: SELECT session_id, text FROM sys.dm_exec_requests Read More...
How do I find the data types such as xml or text or image of a specified table?
18 July 07 07:34 AM | SQL Master | 1 Comments   
USE <database_name>; GO SELECT name AS column_name ,column_id ,TYPE_NAME(user_type_id) AS type_name ,max_length ,CASE WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml' THEN 1 ELSE 0 END AS [(max)] FROM sys.columns WHERE object_id=OBJECT_ID('<schema_name.table_name>') Read More...
Filed under: , , , , ,
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...