sql server list all tables

=columnproperty (a.id,a.name,' PRECISION '), Number of decimal digits =IsNull(ColumnProperty (a.id,a.name,' scale '), 0), Allow null = case Time a.isnullable=1 then ' √ 'else ' end, Default value =isnull(E.text,"), Field Description =isnull(G.[value],") from Syscolumns a Left Join systypes b on A.xusertype=b.xusertype Inner join sysobjects D on (a.id=d.id)and (d.xtype=' U ')and (d.name ' dtproperties ') Left Join syscomments e on a.cdefault=e.id Left join sys

Label: If table failure is deleted because of a foreign KEY constraint, all constraints are removed first:--/1th Step ********** Remove foreign KEY constraints for all tables *************************/ DECLAREC1cursor for Select 'ALTER TABLE ['+ object_name(parent_obj)+ '] Drop constraint ['+Name+']; ' fromsysobjectswhereXtype= 'F' OpenC1Declare @c1 varchar(80

1, using the sysobjects system table in this table, each object created in the database (for example, constraints, defaults, logs, rules, and stored procedures) has a row, and we filter out all the records in the table that are xtype equals u, which is the table in the database. The example statements are as follows:SELECT * from sysobjects where xtype= ' U 'Note: In SQL SERVER2005, the sys.objects catalog

key endB.name astype, A.length asthe number of bytes consumed,ColumnProperty(a.ID, A.name,'PRECISION') aslength,ISNULL(ColumnProperty(a.ID, A.name,' Scale'),0) asNumber of decimal digits, ( Case whenA.isnullable= 1 Then '√' ELSE "' END) asallow NULL,ISNULL(E.text,"') asdefault value,ISNULL(g.[value],"') asField Description fromsyscolumns a Left JOINSystypes b onA.xtype=B.xusertypeINNER JOINsysobjects D ona.ID=d.id andD.xtype= 'U' andD.

Excel|server SET ansi_nulls off Go SET NOCOUNT on Go SET LANGUAGE ' Simplified Chinese ' Go DECLARE @tbl nvarchar (@fld nvarchar), @sql nvarchar (4000), @maxlen int, @sample nvarchar (40) SELECT d.name tablename,a.name fieldname,b.name typename,a.length length,a.isnullable into #t From Syscolumns A, systypes b,sysobjects D WHERE A.xtype=b.xusertype and A.id=d

1. Get all database names:Select name from Master .. sysdatabases order by name2. Get all table names:Select name from databasename .. sysobjects where xtype = 'U' order by nameXtype = 'U': indicates all user tables;Xtype = 's': indicates all system

1. Get all database names: SELECT name fromMaster.. sysdatabases ORDER by Name2. Get all table names: SELECT name fromDatabaseName. SysObjects Where xtype='U'ORDER by Namextype='U': represents all user tables; XType='S': represents all system

The test is important. We can know which tables are affected by the current transaction-used to record when and what operations the user performs on the current table: update, insert, deletecreatetableTriggerRecord (operdtdatetime, -- trigger time opertpvarchar (10), -- operation type: update, insert, deleteope The test is important. We can know which tables are affected by the current transaction-used to r

Query all databases Select [name] from [sysdatabases] order by [name] Query all tables in a database Select [id], [name] from [sysobjects] where [type] = 'U' We all know that [type] = 'U' is a user table, and [type] = 's' is a system table. Based on the query result of the previous statement Query

Query all databases Select [name] from [sysdatabases] Order by [name] Query all tables in a database Select [ID], [name] from [sysobjects] Where [type] = 'U' We all know that [type] = 'U' is a user table, and [type] = 's' is a system table. Based on the query result of the previous statement Query

如果由于外键约束删除table失败，则先删除所有约束：--/第1步**********删除所有表的外键约束*************************/DECLAREc1cursorforselect‘alter table [‘+ object_name(parent_obj) +‘] drop constraint [‘+name+‘]; ‘fromsysobjectswherextype =‘F‘openc1declare@c1varchar(8000)fetchnextfromc1into@c1while(@@fetch_status=0)beginexec(@c1)fetchnextfromc1into@c1endclosec1deallocate c1--/第2步**********删除所有表*************************/use 数据库名(是要删除表的所在的那个数据库的名称)GOdeclare@sqlvarchar(8000)while (selectcount(*)fromsysobjectswhere type=‘U‘)>0beginSELE

, such as the table TB Name field value: John, Dick, Harry, Zhao Liu And we want to query the name= John, this must be judged when the table's name field contains the query criteria, that is, "John", that is, the query Workaround: 1, the first thought of course is like query (not the ultimate solution, the ultimate way to continue to look down) such as sql= "select * Form TB where tb.name like '%" John "%" To explain this statement, you can que

forward way. You can generate DDL database scripts. This document describes reverse engineering. Start! First open EA, create a class chart, class digoal, right-click, "Import dB schema from ODBC" Then click the database name behind the database name... Button. The ODBC link is displayed. Select the tab page of machine data source and click "new. When selecting ODBC driver, note that "SQL native client" should be selected for sqlserver2005, an

Label:This article from: http://www.cnblogs.com/yjss/articles/2065664.html 1. Delete a table Use database name declare mycur cursor local for SELECT [name] from dbo.sysobjects where xtype= ' U ' declare @name varchar (10 0) OPEN mycur FETCH NEXT from Mycur to @name while @ @FETCH_STATUS = 0 BEGIN exec (' drop table ' + @na Me) FETCH NEXT from mycur to @name END CLOSE mycur 2. Delete a view Use database name declare mycur cursor local for SELECT [name] from dbo.sysobjects where xtype=

Easy to delete all the data tables in the database, empty the database, some constraints, not directly delete, you need to delete the constraints in the library, the code is as follows Copy Code code as follows: --Delete all constraints DECLARE C1 Cursor FOR Select ' ALTER TABLE [' + object_name (parent_obj) + '] drop constraint [' +name+ ']; '

Display information for all tables or views in a database in a SQL ServerThe difference between SQL Server 2000 and 2005 is in the Red Word sectionThe following statement takes all table information and replaces the green bold wor

From: http://www.cnblogs.com/chillsrc/archive/2008/04/15/1154186.html I. Environment and test requirements 1. Test Environment ① Hardware: CPU: 2 GB, memory: 2 GB ② Database: SQL 2005 The result varies depending on the machine configuration. 2. Performance Test Description ① Query after the database server is restarted, that is, the result of the first query.② Compare the performance of databa

Querying all the Databases Select [Name] from [sysdatabases] ORDER by [name] Querying for all tables in a database Select [ID], [name] from [sysobjects] where [type] = ' u ' all know [Type] = ' u ' is the user table, [type] = ' s ' is the system table. Based on the query results from the previous statement, and th

You are working on a program for importing an Excel file from a SQL Server database. To read the columns in the database information, looking for a lot of information from the Internet, and finally summed up the more ideal SQL statements, after the execution of the returned columns are: Table name, column name, column type, column length, column description, whet

You are working on a program for importing an Excel file from a SQL Server database. To read the columns in the database information, looking for a lot of information from the Internet, and finally summed up the more ideal SQL statements, after the execution of the returned columns are: Table name, column name, column type, column length, column description, whet