Browse by Tags

Replication FAQ series - Which columns are published in snapshot or transactional or merge publications in this database?

Published 11 December 7 6:48 PM | SSQA.net

Here is script you can find which columns are published in the replication that is using either snapshot or transactional or merge publications. --For snapshot or transactional publications select object_name(object_id) as tran_published_table, name as...

Filed under: sql server, replication, snapshot, merge, transactional, tsql, tables, columns, FAQ

Getting table name from column name

Published 15 September 7 7:23 AM | SSQA.net

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...

Filed under: sql server, tsql, tables, best practice, columns, information schema

TSQL to find all triggers in a database including their count

Published 23 August 7 3:28 AM | SSQA.net

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' ,...

Filed under: sql server, version, trigger, tsql, tables

Display the server-level information the application role can currently view.

Published 9 August 7 5:28 AM | SSQA.net

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 ) Read More...

Filed under: performance, view, tsql, tables, application role

List tables that do not have primary keys

Published 2 August 7 6:35 AM | SSQA.net

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...

Filed under: tsql, tables, primary key

TSQL to generate a script that updates a column on every table in my database. How do I do this?

Published 29 July 7 3:39 AM | SSQA.net

SELECT 'ALTER TABLE ' + sysobjects.name + ' ' + 'ALTER COLUMN ' + syscolumns.name + ' ' + systypes.name + '(' + cast(syscolumns.length as varchar) + ') ' + 'COLLATE ' + syscolumns.collation + '...

Filed under: change, sql server, databases, tables, collation

TSQL to generate blocking scenario for testing

Published 27 July 7 2:44 AM | SSQA.net

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...

Filed under: sql server, blocking, query, script, tsql, tables, create

Retrieve SessionID and batch information

Published 24 July 7 4:37 AM | SSQA.net

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...

Filed under: performance, cache, tsql, tables, object_name, forced

How do I find the data types such as xml or text or image of a specified table?

Published 18 July 7 9:34 AM | SSQA.net

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...

Filed under: xml, datatype, tsql, tables, image, lob

How do I find all the tables and indexes that are partitioned?

Published 17 July 7 4:42 AM | SSQA.net

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...

Filed under: sql server, indexes, trigger, tables, partition

TSQL to return (display) all tables row count in database - think about performance

Published 16 July 7 4:5 AM | SSQA.net

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...

Filed under: sql server, performance, tsql, rowcount, tables, display